JDBC工具类
- 注意：下面的工具类皆是在DBUtil类下的工具类即：
java">
public class DBUtil {
protected Connection getconn(){}
protected void closeall(){}
protected void myexecuteUpdate(String sql,Object... params){}
protected void myexecuteQuery(String sql,Object... params){}
protected List<Student> myexecuteQuerylist(String sql,Object... params){}
}
连接工具类和释放工具类
工具类的code：
java">package com.mystudy.jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class DBUtil {
protected Connection conn=null;
protected PreparedStatement ps=null;
protected ResultSet rs=null;
protected Connection getconn() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/test1?useSSL=true","root","root");
return conn;
}
protected void closeall(){
try {
if (rs!=null){rs.close();}
if (ps!=null){ps.close();}
if (conn!=null){conn.close();}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
rs=null;
ps=null;
conn=null;
}
}
protected void myexecuteUpdate(String sql,Object... params){}
protected void myexecuteQuery(String sql,Object... params){}
protected List<Student> myexecuteQuerylist(String sql,Object... params){}
}
工具类的使用：
- 注意：
1. 在本类中可以用关键字this调用
2. 在通常情况下该DBUtil类应是abstract类型，
3. 为了方便测试，故将abstract去掉
4. 具体abstract类型在具体问题中会有具体的继承DBUtil的子类供测试
增删改工具类：
增删改工具类的code：
java">
protected void myexecuteUpdate(String sql,Object... params){
try {
this.getconn();
ps=conn.prepareStatement(sql);
if (sql!=null&¶ms.length!=0){
for (int i=0;i<params.length;i++){
ps.setObject(i+1,params[i]);
}
}
if (ps.executeUpdate()>0){
System.out.println("操作成功");
}else {
System.out.println("操作失败");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
this.closeall();
}
}
增删改工具类的使用：
java"> public static void main(String[] args) {
String sql="UPDATE student SET stu_name=? WHERE stu_id=?";
DBUtil dbUtil=new DBUtil();
dbUtil.myexecuteUpdate(sql,"新人",7);
}
- 在具体方法中，由于DBUtil应是abstract类，所以由它的子类在测试类中new出来，然后得到该方法
查询工具类方法一：
查询工具类的code：
java"> protected void myexecuteQuery(String sql,Object... params){
try {
this.getconn();
ps=conn.prepareStatement(sql);
if (sql!=null&¶ms.length!=0){
for (int i=0;i<params.length;i++){
ps.setObject(i+1,params[i]);
}
}
rs=ps.executeQuery();
while (rs.next()){
System.out.print(rs.getInt(1));
System.out.print(rs.getString(2));
System.out.print(rs.getString(3));
System.out.print(rs.getDate(4));
System.out.print(rs.getString(5));
System.out.println(rs.getString(6));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
this.closeall();
}
}
查询工具类的使用：
java"> public static void main(String[] args) {
String sql="SELECT * FROM student";
DBUtil dbUtil=new DBUtil();
dbUtil.myexecuteQuery(sql);
}
java"> public static void main(String[] args) {
String sql="SELECT * FROM student where stu_id=?";
DBUtil dbUtil=new DBUtil();
dbUtil.myexecuteQuery(sql,7);
}
查询工具类方法二：
查询工具类二的code：
java">
protected List<Student> myexecuteQuerylist(String sql,Object... params){
List<Student> list= null;
try {
list = new ArrayList<>();
this.getconn();
ps=conn.prepareStatement(sql);
if (sql!=null&¶ms.length!=0){
for (int i=0;i<params.length;i++){
ps.setObject(i+1,params[i]);
}
}
rs=ps.executeQuery();
while (rs.next()){
Student student=new Student();
student.setStu_id(rs.getInt(1));
student.setStu_name(rs.getString(2));
student.setStu_sex(rs.getString(3));
student.setStu_birthday(rs.getDate(4));
student.setStu_user(rs.getString(5));
student.setStu_password(rs.getString(6));
list.add(student);
}
return list;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
this.closeall();
}
return list;
}
查询工具类二的使用：
java"> public static void main(String[] args) {
String sql="SELECT * FROM student";
DBUtil dbUtil=new DBUtil();
System.out.println(dbUtil.myexecuteQuerylist(sql));
}
java"> public static void main(String[] args) {
String sql="SELECT * FROM student where stu_id=?";
DBUtil dbUtil=new DBUtil();
System.out.println(dbUtil.myexecuteQuerylist(sql, 7));
}
查询工具类二注意：
- 使用查询二需要将数据库表中的表头写在Java程序中如：
所需code：
java">package com.mystudy.jdbc;
import java.util.Date;
public class Student {
private int stu_id;
private String stu_name;
private String stu_sex;
private Date stu_birthday;
private String stu_user;
private String stu_password;
public int getStu_id() {return stu_id;}
public void setStu_id(int stu_id) {this.stu_id = stu_id;}
public String getStu_name() {return stu_name;}
public void setStu_name(String stu_name) {this.stu_name = stu_name;}
public String getStu_sex() {return stu_sex;}
public void setStu_sex(String stu_sex) {this.stu_sex = stu_sex;}
public Date getStu_birthday() {return stu_birthday;}
public void setStu_birthday(Date stu_birthday) { this.stu_birthday = stu_birthday;}
public String getStu_user() {return stu_user;}
public void setStu_user(String stu_user) {this.stu_user = stu_user;}
public String getStu_password() {return stu_password;}
public void setStu_password(String stu_password) {this.stu_password = stu_password;}
@Override
public String toString() {
return "Student{" +
"stu_id=" + stu_id +
", stu_name='" + stu_name + '\'' +
", stu_sex='" + stu_sex + '\'' +
", stu_birthday=" + stu_birthday +
", stu_user='" + stu_user + '\'' +
", stu_password='" + stu_password + '\'' +
'}'+'\n';
}
}
- 工具类学完基本JDBC差不多了，可以了解一下：sql注入，，，事务回滚
