JDBC 封装增,删,改方法 第二节

1.首先需要把数据库的连接信息抽取到properties文件中

        在src根目录下创建一个db.properties文件   （文件命名可以随便起）

 

 在文件中写入信息内容：

username=root
password=123456
url=jdbc:mysql://localhost:3306/book?serverTimezone=Asia/Shanghai
driverName=com.mysql.cj.jdbc.Driver

注意：=后不能使用""

        读取属性文件中的内容

    public static String username;
    public static String password;
    public static String url;  //数据库地址
    public static String driverName;   //驱动名

    static{
        InputStream 	inputStream=BaseDao.class.getResourceAsStream("/db01.properties");
        //加载属性文件和读取属性文件中的内容
        Properties properties =new Properties();
        try {
            properties.load(inputStream);
            username = properties.get("username").toString();
            password = properties.get("password").toString();
            url = properties.get("url").toString();
            driverName = properties.get("driverName").toString();
            Class.forName(driverName);
        }catch (Exception e){
            e.printStackTrace();
        }

2.BaseDao(父类)  增删改抽取

public int update(String sql,Object...parpam){
        int row=-1;
        try {
            getConnection();
            ps = conn.prepareStatement(sql);
            //为占位符赋值
            //parpam:相当于一个数组，里面存放了多少个占位符
            for (int index = 0; index < parpam.length; index++) {
                ps.setObject(index + 1, parpam[index]);
            }
           row= ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return row;
    }

3.父类中全部封装内容

package until;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * @ClassName BaseDao2
 * @author:zkm
 * @Date: 2021-10-22 13:42
 */
 
 //抽取父类
public class BaseDao2 {
	//连接数据库的对象
    public Connection conn;
    public PreparedStatement ps;
    public ResultSet resultSet;

	//数据库连接的信息
    public static String username;
    public static String password;
    public static String url;
    public static String drivername;

	//读取数据库连接信息和加载驱动
    static {
        InputStream inputStream=BaseDao2.class.getResourceAsStream("/dbc.properties");
        Properties properties=new Properties();
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        username= properties.getProperty("username");
        password= properties.getProperty("password");
        url= properties.getProperty("url");
        drivername= properties.getProperty("drivername");
        try {
            Class.forName(drivername);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

	//获取连接对象
    public void getConnection() throws SQLException {
        conn= DriverManager.getConnection(url,username,password);
    }

	//增删改的公共方法
    public int update(String sql,Object...parpam){
        int row=-1;
        try {
            getConnection();
            ps = conn.prepareStatement(sql);
            //params:占位符参数的值
            for (int index = 0; index < parpam.length; index++) {
                ps.setObject(index + 1, parpam[index]);
            }
           row= ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return row;
    }
	
	//关闭资源
    public void colesAll(){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

4.写一个dao层里面写一个子类BookDao来继承父类的方法

package dao;

import entil.User;
import until.BaseDao2;

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

/**
 * @ClassName BookDao2
 * @author:zkm
 * @Date: 2021-10-22 13:42
 */
public class BookDao2 extends BaseDao2 {
    //添加
    public int insert(User user){
        return update("insert into user(id,usnam,password) values (?,?,?)",user.getId(),user.getUsnam(),user.getPassword());
    }
    //删除
    public int detele(int id ){
        return update("delete from user where id=?",id);
    }
    //修改
    public int update(User user){
        return update("update user set name=? where id=?",user.getUsnam(),user.getId());
    }

    //查询全部内容
    public List<User> finAll(){
        List<User> list=new ArrayList<>();
        try {
            getConnection();
            ps = conn.prepareStatement("select* from user");
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("usnam");
                String password = resultSet.getString("password");
                User user = new User(id, name, password);
                list.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    //单行查询
    public User fin(int id ){
            User user=null;
        try {
            getConnection();
            ps = conn.prepareStatement("select * from user where id=?");
            ps.setObject(1,id);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id1 = this.resultSet.getInt("id");
                String name = this.resultSet.getString("usnam");
                String password = this.resultSet.getString("password");
                 user = new User(id1, name, password);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

}

5.在测试类中实现

package Text;

import dao.BookDao2;
import entil.User;
import org.junit.Test;

import java.util.List;

/**
 * @ClassName Text2
 * @author:zkm
 * @Date: 2021-10-22 13:42
 */
public class Text2 {
    BookDao2 bookDao2=new BookDao2();
    @Test
    //添加
    public void text1(){
        User user=new User(3,"找","123");
        int row= bookDao2.insert(user);
        System.out.println(row);
    }
    @Test
    //删除
    public void text2(){

        System.out.println(bookDao2.detele(3));
    }

    //修改
    @Test
    public void text3(){
        User user=new User(2,"赵柯梦","123");
        int row= bookDao2.update(user);
        System.out.println(row);
    }

    //多行行查询
    @Test
    public void text4(){
        List<User> list= bookDao2.finAll();
        for (User s:list ) {
            System.out.println(s);
        }
    }

    //单行查询
    @Test
    public void text5(){
        User user= bookDao2.fin(1);
        System.out.println(user);
    }
}