1、JDBC概述
Java DataBase Connectivity,属于J2EE开发技术之一.
而相关的API规范却在JDK中:
1.java.sql.*;
2.javax.sql.*;
作用:SUN公司为了简化,统一对数据库的操作,定义了一套Java操作数据库的规范,称之为JDBC.而相对应厂商提供的数据库驱动来实现SUN公司提供的规范标准.
2、JDBC的开发编码步骤
1.搭建开发环境,将数据库驱动加载到系统classpath下,相对应的版本号要一致.
2.程序中注册驱动,有两种方式实现.
--------DriverManager.registerDriver(new com.mysql.jdbc.Driver());
注意:查看Mysql驱动源代码可以知晓,Mysql的驱动类实际上加载了两次.第二点就是严重依赖数据库驱动,不如字符串灵活(在配置文件中).所以不常用此种方式加载数据库驱动.
---------Class.forName("com.mysql.jdbc.Driver");
注意:常用的方式加载驱动类.
3.获取与数据库的连接.
---------Connection conn = DriverManager.getConnection(url,user,password)
----------url:jdbc:mysql://localhost:3306/day16
--------user:root
----password:root
4.创建代表SQL语句的对象.
--------Statement stnt = conn.createStatement();
5.执行SQL语句.
--------Result executeQuery("查询语句");
//DDL
-----int executeUpdate("其他语句"); //影响记录的行数.DML
------boolean execute("任意语句");//有返回结果集,则返回true;如果没有结果,则返回false.
6.如果执行的是查询语句的话,返回结果集.然后遍历结果集.
--------while(rs.next){
------//to do something;//Result对象有关方法是重点,后面详解.
---------}
7.关闭释放资源.
实际上MySQL官方文档上提供了不错的模板代码,当然一般是执行在finally语句中:
if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { // ignore } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { // ignore } stmt = null; } if (conn!= null) { try { conn.close(); } catch (SQLException sqlEx) { // ignore } conn= null; }
|
3、JDBC规范中常用类和接口(JDK1.6文档的重要性)
1.DriverManager
2.URL的详细说明
3.Connection(接口,由数据库驱动类实现该接口)
4.Statement
5.ResultSet
4、MySQL和Java数据类型的对应关系
5、JDBC中的简单CRUD
1.封装自己的简单JDBCUtil工具类. a.配置文件 driverName=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/day15username=rootpassword=lisai b.JDBCUtil工具类 package com.itheima.utils;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;public class JdbcUtil { private static String driver; private static String url; private static String username; private static String password; static { try { Properties props = new Properties(); InputStream in = JdbcUtil.class.getClassLoader() .getResourceAsStream("jdbc.properties"); props.load(in); driver = props.getProperty("driverName"); url = props.getProperty("url"); username = props.getProperty("username"); password = props.getProperty("password"); Class.forName(driver); } catch (Exception e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection() { try { Connection conn = DriverManager.getConnection(url, username, password); return conn; } catch (Exception e) { throw new RuntimeException(e); } } public static void release(Connection conn, Statement stat, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } rs = null; } if (stat != null) { try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } stat = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } conn = null; } }}c.单表的CRUD操作 package com.itheima.jdbc;import java.sql.Connection;import java.sql.ResultSet;import java.util.Date;import java.sql.Statement;import org.junit.Test;import com.itheima.utils.JdbcUtil;public class JdbcCURD { @Test public void testAdd(){ Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); stat = conn.createStatement(); Date date = new Date(); stat.executeUpdate("insert into tb_user(username,password,email,birthday) values('itheima','123456','lisx@sina.com','"+date.toLocaleString()+"')"); } catch (Exception e) { e.printStackTrace(); } finally{ JdbcUtil.release(conn, stat, rs); } } @Test public void testUpdate(){ Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); stat = conn.createStatement(); Date date = new Date(); stat.executeUpdate("update tb_user set password='bbb' where username='itheima'"); } catch (Exception e) { e.printStackTrace(); } finally{ JdbcUtil.release(conn, stat, rs); } } @Test public void testDelete(){ Connection conn = null; Statement stat = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); stat = conn.createStatement(); stat.executeUpdate("delete from tb_user where username='itheima'"); } catch (Exception e) { e.printStackTrace(); } finally{ JdbcUtil.release(conn, stat, rs); } }} |
6、PreparedStatement
| 1.简单使用PreparedStatement对象 String sql = "insert into tb_user(username,password,email,birthday) values(?,?,?,?)"; PreparedStatement stmt = conn.prepareStatement(sql); stmt.setString(1, user.getUsername()); stmt.setString(2, user. getPassword()); stmt.setString(3, user.getEmail()); stmt.setDate(4, new java.sql.Date(user.getBirthday().getTime())); stmt.executeUpdate(); 2.使用PreparedStatement实现上面的单表CRUD操作. package com.itheima.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import com.itheima.dao.UserDao;import com.itheima.domain.User;import com.itheima.utils.JdbcUtil;public class UserDaoJdbcImpl implements UserDao { @Override public User findUserByName(String username) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); String sql = "select username,password,email,birthday from tb_user where username=?"; stmt = conn.prepareStatement(sql); stmt.setString(1, username); rs=stmt.executeQuery(); while(rs.next()){ User user = new User(); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setBirthday(rs.getDate("birthday")); return user; } return null; } catch (Exception e) { throw new RuntimeException(e); } finally{ JdbcUtil.release(rs, stmt, conn); } } @Override public User findUserByNameAndPassword(String username, String password) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); String sql = "select username,password,email,birthday from tb_user where username=? and password=?"; stmt = conn.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); rs=stmt.executeQuery(); if(rs.next()){ User user = new User(); user.setUsername(rs.getString("username")); user.setPassword(rs.getString("password")); user.setEmail(rs.getString("email")); user.setBirthday(rs.getDate("birthday")); return user; } return null; } catch (Exception e) { throw new RuntimeException(e); } finally{ JdbcUtil.release(rs, stmt, conn); } } @Override public void addUser(User user) { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = JdbcUtil.getConnection(); String sql = "insert into tb_user(username,password,email,birthday) values(?,?,?,?)"; stmt = conn.prepareStatement(sql); stmt.setString(1, user.getUsername()); stmt.setString(2, user.getPassword()); stmt.setString(3, user.getEmail()); stmt.setDate(4, new java.sql.Date(user.getBirthday().getTime())); stmt.executeUpdate(); } catch (Exception e) { throw new RuntimeException(e); } finally{ JdbcUtil.release(rs, stmt, conn); } }}3.使用PreparedStatement的优势.
|
![QQ拼音截图未命名 [8]](http://static.oschina.net/uploads/img/201504/10105310_4HTY.png "QQ拼音截图未命名 [8]")
; 7、工厂模式解耦程序
| 新的问题:在学习JDBC之前,项目中就用XML作为DAO层(数据访问层)的对象.所以相对应的实现DAO就是基于XML编程滴？而关系型数据库相对于XML文档更具有存储数据的优势.而且数据库使用的不同,相对应的DAO实现也会不同.以后还会使用O/R Mapping持久化框架(Hibernate, iBatis)处理数据的存储？ 答:通过接口和工厂模式来解耦整个程序.达到分层的效果. 工厂模式的分类:静态工厂,动态工厂的设计. 这里以静态工厂为例: 1.由于这个工厂整个应用中只能存在一个,所以设计成单例. 2.提供动态方法让外界获取实现,而具体实现类由配置文件来指定(不同需求,指定不同实现类). package com.itheima.utils;import java.util.ResourceBundle;import com.itheima.dao.CustomerDao;import com.itheima.service.CustomerService;public class BeanFactoryUtil { private BeanFactoryUtil(){} private static BeanFactoryUtil instance = new BeanFactoryUtil(); public static BeanFactoryUtil getInstance(){ return instance; } private static ResourceBundle bundle = null; static { bundle = ResourceBundle.getBundle("beans"); } /** * 获取实例化数据访问层Bean * @return CustomerDao */ public CustomerDao getCustomerDaoImpl(){ try { String className = bundle.getString("customerDao"); CustomerDao customerDao=(CustomerDao) Class.forName(className).newInstance(); return customerDao; } catch (Exception e) { throw new IllegalArgumentException("不能实例化Bean."); } } /** * 获取实例化逻辑业务Bean * @return CustomerDao */ public CustomerService getCustomerServiceImpl(){ try { String className = bundle.getString("customerService"); CustomerService customerService=(CustomerService) Class.forName(className).newInstance(); return customerService; } catch (Exception e) { throw new IllegalArgumentException("不能实例化Bean."); } }}#DAO层实现类指定.customerDao=com.itheima.dao.impl.CustomerDaoJdbcImpl#SERVICE层类指定.customerService=com.itheima.service.impl.CustomerServiceImpl 3.如果由下一层调用获取. private CustomerDao customerDao = BeanFactoryUtil.getInstance().getCustomerDaoImpl(); 动态工厂设计:不设置成单例模式,而使用静态方法有外界获取实现. |
END