极致优化的JDBC操作

JDBC操作表格

//操作步骤：
//1.创建数据表
//2.新建实体类
//3.编写工具类
//4.将jdbc抽象到dao层
//5.在测试类中编写业务逻辑
首先你得用于自己的表格，类似于这种:

工具类：包括连接数据库、关闭资源等公用的操作。

package dao;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtils {
    private static Properties p = new Properties();
    private static ThreadLocal<Connection> th=new ThreadLocal<Connection>();
    //静态代码块：只加载一次
    static{
        //反射对象调用getResourceAsStream
        //从src目录下获取到db.properties的资源
        try {
            InputStream is = DBUtils.class.getResourceAsStream("/db.properties");
            p.load(is);
            Class.forName(p.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection conn = null;
        try {
            conn=th.get();
            if(conn==null){
                conn = DriverManager.getConnection(p.getProperty("url"), p.getProperty("username"), p.getProperty("password"));
                th.set(conn);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    //开启事务
    public static void begin(){
        Connection connection = getConnection();
        try {
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    //提交事务
    public static void commit(){
        Connection connection = getConnection();
        try {
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,null,null);
        }
    }
    //回滚事务
    public static void rollback(){
        Connection connection = getConnection();
        try {
            connection.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(connection,null,null);
        }
    }

    public static void closeAll(AutoCloseable...cs){
        for(AutoCloseable c : cs){
            if(c!=null){
                try {
                    c.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        th.remove();//移除th对象,空间没用了，所以关闭，提升性能
    }
}

表格属性类：抽取表格属性

package dao;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;//在java中，导入util.Date的日期类
//实体类中的属性要与表字段一致--ORM
@Data
@NoArgsConstructor
@AllArgsConstructor

public class Person {
        private int id;
        private String name;
        private  int age;
        private Date bornDate;
        private  String email;
        private  String address;
    }

数据访问层：JDBC的相关操作

package dao;



import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//dao层--数据访问层（用于做jdbc操作）
public class PersonDaoimpl {
    public int insert(Person p) {
        String sql = "insert into person(name,age,bornDate,email,address) values(?,?,?,?,?)";
        return DaoUtils.commonUpdate(sql,p.getName(),p.getAge(),p.getBornDate(),p.getEmail(),p.getAddress());
    }


    public int update(Person person){
        Connection conn=null;
        PreparedStatement prst=null;
        try {
           conn= DBUtils.getConnection();
            prst = conn.prepareStatement("update person set name=?,age=? where id=?");
            prst.setString(1,person.getName());
            prst.setInt(2,person.getAge());
            prst.setInt(3,person.getId());
            return prst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            DBUtils.closeAll(prst,conn);
        }
        return  0;
    }
    public int delete(int id){
        Connection conn=null;
        PreparedStatement prst=null;
        try {
            prst=conn.prepareStatement("delete from person where id=?");
            prst.setInt(1,id);
            return prst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  0;
    }
    public Person selectById(int i){
        Connection conn=null;
        PreparedStatement prst=null;
        try {
            prst=conn.prepareStatement("select * from Person where id=?");
            prst.setInt(1,i);
            ResultSet rs = prst.executeQuery();
            Person person=new Person();
            while (rs.next()) {
                person.setId(rs.getInt("id"));
                person.setName(rs.getString("name"));
                person.setAge(rs.getInt("age"));
                person.setBornDate(rs.getDate("bornDate"));
                person.setEmail(rs.getString("email"));
                person.setAddress(rs.getString("address"));
            }
            return person;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  null;
    }public List<Person> selectAll(){
        Connection conn=null;
        PreparedStatement prst=null;
        List<Person> list=new ArrayList<>();
        try {
            prst= conn.prepareStatement("select * from person");
            ResultSet rs = prst.executeQuery();
            while(rs.next()){
                Person person = new Person();
                person.setId(rs.getInt("id"));
                person.setName(rs.getString("name"));
                person.setAge(rs.getInt("age"));
                person.setBornDate(rs.getDate("bornDate"));
                person.setEmail(rs.getString("email"));
                person.setAddress(rs.getString("address"));
                list.add(person);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
        return list;
    }

}

优化增删改查层--------使其通用

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class DaoUtils {
    public static int commonUpdate(String sql, Object... args) {
        Connection conn=null;
        PreparedStatement pr=null;
        try {
            conn= DBUtils.getConnection();
            pr=conn.prepareStatement(sql);
            for(int i=0;i<args.length;i++){
                pr.setObject(i+1,args[i]);
            }
            return pr.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtils.closeAll(pr,conn);
        }
        return 0;
    }
}

业务层：收集用户操作数据

package dao;

import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class service {
    private final  PersonDaoimpl per=new PersonDaoimpl();
    public  int Test(int num){
        switch (num) {
            case 1://添加传对象，一般主键自增长，id不用管
            //当然还可以进一步优化  键盘输入下列值
                int result=(per.insert(new Person(0,"nq",20,new Date(),"ls@163.com","湖北")));
                System.out.println("插入："+result);
                break;

            case 2: //修改传对象，要有修改的主键id，才能修改
                result=per.update(new Person(1,"ls",44,new Date(),"zs@163.com","湖南"));
                System.out.println("修改："+result);
                break;

            case 3:

                result=per.delete(3);
                System.out.println("删除:"+result);
                break;

            case 4: //查询所有，返回List集合
                List<Person> list=per.selectAll();
                System.out.println("查所有："+list);
                break;

            case 5://查询单个，往往根据id查，返回实体对象
                Person person=per.selectById(2);
                System.out.println("查对象："+person);
                break;

            default:
                System.out.println("你的输入有误！！！");
                break;
        }
        return 0;
    }
}

测试层：在测试类中，进行业务逻辑操作

package dao;

import java.util.Date;
import java.util.List;
import java.util.Scanner;
/*
#### 创建数据表
        > - 创建一张表 Person，有以下列：
        >   - id：int，主键，自动增长
        >   - name：varchar(20) 非空
        >   - age：int 非空
        >   - bornDate：Date
        >   - email：字符串
        >   - address：字符串
*/
//在测试类中，进行业务逻辑操作
//操作步骤
//1.创建数据库
//2.新建实体类
//3.编写工具类
//4.将jdbc到dao
//5.在测试类中编写业务逻辑
public class test1 {
    public static void main(String[] args) {
        System.out.println("请输入你要进行的操作:1.插入 2.修改 3.删除 4.查所有5.根据id查");
        PersonDaoimpl personDao = new PersonDaoimpl();
        Scanner scanner = new Scanner(System.in);
        int num= scanner.nextInt();
        service ser=new service();
        ser.Test(num);


    }
}