JDBC 单例模式 封装
分开方法执行和 sql语句的传入
封装一个工具类,将执行语句封装起来
在JDBCManager.class中,使用了单例模式
import java.sql.*;
/**
* 设计一个数据库的访问工具类,采用单例模式
*/
public class JDBCManager {
//数据库的用户名
private final String USER ="root";
//数据库密码
private final String PASSWORD="19970909";
//数据库的访问lianjie
private static final String URL="jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=false";
//数据库的驱动
private final String DRIVER="com.mysql.cj.jdbc.Driver";
//获取JDBCManager 的对象
private static JDBCManager instance;
//数据库的执行空间
private Statement statement;
//数据库的连接
private Connection connection;
/**
* 构造方法私有化
*/
private JDBCManager(){
try {
Class.forName(DRIVER);
System.out.println("注册MySQL驱动成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 单例模式构建访问数据库的访问对象
* @return
*/
public static JDBCManager getInstance(){
if(instance == null){
instance = new JDBCManager();
}
return instance;
}
/**
* 获得数据库的连接
* @return
*/
public Connection getConnection()throws SQLException {
connection=DriverManager.getConnection(URL,USER,PASSWORD);
return connection;
}
/**
* 执行静态的SQL语句
* @param sql
* @return 返回一个整型,整型值为数据库受影响的行数
* @throws SQLException
*/
public int executeSQLByStatement(String sql) throws SQLException{
statement=connection.createStatement();
return statement.executeUpdate(sql);
}
/**
* 执行DML语句,例如删除,插入,修改
* insert into student()
* @param sql
* @param columnName 列名称
* @return
* @throws SQLException
*/
public int executeSQLBYStatement(String sql,String[] columnName)throws SQLException{
statement =connection.createStatement();
return statement.executeUpdate(sql,columnName);
}
/**
* 查询返回的结果,可能包含一个或者多个结果
* 返回值 参数
* 方法 int executeUpdate(String sql, int[] columnIndexes)
* @param sql
* @return
* @throws SQLException
*/
public ResultSet executeQuetyBYStatement(String sql)throws SQLException{
statement =connection.createStatement();
return statement.executeQuery(sql);
}
/**
*将关闭流放入方法中减少代码重复量
*/
public void closeAll(){
try {
if(statement!=null) {
statement.close();
}
if(connection!=null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
TestStudent.class类 用于测试封装的执行方法类JDBCManager.class
此类中具体写入sql语句
并且在main方法里将sql语句的value真正传入
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class TestStudent {
private JDBCManager manager;
public TestStudent(){
manager =JDBCManager.getInstance();
}
/**
* 执行删除语句的方法
*/
public void delete(){
String sql ="delete from student where id =2";
try {
manager.getConnection();
int row_result = manager.executeSQLByStatement(sql);
//判断受影响的行数,若不等于0则删除成功
if(row_result>0){
System.out.println("删除学生记录成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
manager.closeAll();
}
}
/**
* 执行插入语句
* @param val 传入的参数是一个Object类型的数组,在调用insert()方法的时候就需要把参数传入
* 以便以执行SQL语句
*/
public void insert(Object[] val){
//需要外传值来补全SQL语句
String sql = "insert into student(name,age)values('"+val[0]+"',"+val[1]+")";
try {
manager.getConnection();
//这里的String 是上面SQL中的
int row_count = manager.executeSQLBYStatement(sql,new String[]{"name","age"});
if(row_count>0){
System.out.println("添加学生成功");
}
System.out.println("--->"+sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* Statement 是静态的,为了实现能够将SQL语句写成可变的,传入Object[] 数组 次数在中写入 fielsName 对应的 value
* @param val
*/
public void update(Object[] val){
String sql = "update student set name = '"+val[0]+"' ,age="+val[1]+" where id="+val[2]+" ";
System.out.println("--->"+sql);
try {
manager.getConnection();
int row_count = manager.executeSQLBYStatement(sql,new String[]{"name","age","id"});
if(row_count>0){
System.out.println("修改学生成功");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 查询语句
*/
public void querySingleResult(){
String sql ="select name,age from student where id=5";
System.out.println("--->"+sql);
try {
manager.getConnection();
//返回查询的结果集
ResultSet resultSet =manager.executeQuetyBYStatement(sql);
while (resultSet.next()){
System.out.println("-->"+resultSet.getString("name"));
System.out.println("-->"+resultSet.getInt("age"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 查询单个结果
* 创建一个响应的类来接受查询到的返回值,并且输出这个类的引用
*/
public Student queryOneResult(){
Student stu = new Student();
String sql ="select name,age from where id=5";
System.out.println("--->"+sql);
ResultSet resultSet=null;
try {
manager.getConnection();
resultSet = manager.executeQuetyBYStatement(sql);
if(resultSet.next()){
stu.setName(resultSet.getString("naame"));
stu.setAge(resultSet.getInt("age"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
manager.closeAll();
}
return stu;
}
/**
* 查询多个结果
* @return List<Student> list
*/
public List<Student> queryMoreResult(){
String sql = "select id, name ,age from student";
List<Student> list = new ArrayList<>();
ResultSet resultset =null;
try {
manager.getConnection();
resultset =manager.executeQuetyBYStatement(sql);
while(resultset.next()){
Student student =new Student();
student.setAge(resultset.getInt("age"));
student.setId(resultset.getInt("id"));
student.setName(resultset.getString("name"));
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
manager.closeAll();
}
return list;
}
public static void main(String[] args) {
//new TestStudent().insert(new Object[]{"zhangsan", 45});
// new TestStudent().update(new Object[]{"lisi", 66, 5});
//new TestStudent().querySingleResult();
// Student student = new TestStudent().queryOneResult();
// System.out.println(student);
System.out.println(new TestStudent().queryMoreResult());
}
}
Student类
public class Student {
private String name;
private int age;
private int id;
public Student() {
}
public Student(String name, int age, int id) {
this.name = name;
this.age = age;
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Override
public String toString() {
return "Student{" +
"name='" + name + '\'' +
", age=" + age +
", id=" + id +
'}';
}
}