DBUtil

抽取过程

1.写出完整版的JDBC（CRUD）
2.抽取四要素、加载驱动、创建数据库连接、关闭连接、改动操作（曾、删、改）、设置参数、查询操作
3.把上面所以得方法放在DBUtile类中
4.简化JDBC（CRUD）
5.创建Properties配置文件，在DBUtile读取配置文件信息，赋值给四要素

DBUtil.java

public class DBUtil {
 <font color=red size=4>  //连库四要素</font>
private static String driverName;
private static String url;
private static String username;
private static String password;
static{
try {

  <font color=red size=4>  //创建Properties对象</font>
   Properties prop=new Properties();
  
   <font color=red size=4> //创建输入流对象，指向配置文件</font>
   InputStream is= new FileInputStream("config/db.properties");
	<font color=red size=4>//加载</font>
	prop.load(is);
	<font color=red size=4>//获取文件中的数据</font>
	driverName=prop.getProperty("driverName");
	url =prop.getProperty("url");
	username=prop.getProperty("username");
	password=prop.getProperty("password");
	} catch (IOException e) {
	e.printStackTrace();
	 }
}

<font color=red size=4>//加载驱动</font>
private static void driverLoader(){
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

<font color=red size=4>//创建连接对象</font>
public static Connection getConnection(){
try {
driverLoader();

<font color=red size=4>//创建连接对象</font>
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}

<font color=red size=4>//关闭连接对象</font>
public static void closeAll(Connection conn){
if(conn==null) return;
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

<font color=red size=4>//执行改动操作</font>
public static void executeUpdate(Connection conn,String sql,Object... args){
if(conn==null)return;

<font color=red size=4>// 创建预编译对象，同时编写sql语句</font>
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);

<font color=red size=4>//设置参数</font>
setPropretie(pst,args);

<font color=red size=4>// 执行sql语句</font>
pst.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}

<font color=red size=4>//设置参数</font>
private static void setPropretie(PreparedStatement pst,Object... args){
if(pst==null) return;
if(args==null) return;
try {
for(int i=0;i<args.length;i++){
pst.setObject(i+1,args[i]);
}
} catch (SQLException e) {
e.printStackTrace();
}
}

db.properties

driverName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/库名?characterEncoding=utf-8
username=root
password=root

JDBC.java

//预编译操作数据库 添加数据
@Test
public void jdbcAdd() throws SQLException {
// 创建连接对象
Connection conn = DBUtil.getConnection();
String sql =" insert into user(name,age,address,birthday) values(?,?,?,?)";
DBUtil.executeUpdate(conn,sql,"周瑜",25,"吴国",new Date(new
java.util.Date().getTime()));
//释放资源
DBUtil.closeAll(conn);
}

//修改
@Test
public void jdbcUpdate() throws SQLException {
//创建连接对象
Connection conn =DBUtil.getConnection();
String sql ="update user set name=?,age=?,address=?,birthday= ? where id = ? ";
DBUtil.executeUpdate(conn,sql,"孙尚香",22,"吴国",new Date(new
java.util.Date().getTime()),18);
// 释放资源
DBUtil.closeAll(conn);
}

//删除
@Test
public void jdbcDelete() throws SQLException {
Connection conn = DBUtil.getConnection();
String sql =" delete from user where id = ? ";
DBUtil.executeUpdate(conn,sql,3);
DBUtil.closeAll(conn);
}

//查询所有
@Test
public void jdbcQueryAll() throws SQLException {
Connection conn = DBUtil.getConnection();
String sql =" select id,name,age,address,birthday from user ";
//执行查询操作
ResultSet rs = DBUtil.executeQuery(conn, sql);
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
String address = rs.getString(4);
Date birthday = rs.getDate(5);
System.out.println(id+" "+name+" "+age+" "+address+" "+birthday);
}
DBUtil.closeAll(conn);
}

//查询一条
@Test
public void jdbcQueryById() throws SQLException {
Connection conn = DBUtil.getConnection();
String sql =" select * from user where id = ? ";
ResultSet rs =DBUtil.executeQuery(conn,sql,7);
while(rs.next()){
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
String address = rs.getString(4);
Date birthday = rs.getDate(5);
System.out.println(id+" "+name+" "+age+" "+address+" "+birthday);
}
DBUtil.closeAll(conn);
}