jdbc重构（仅供本人参考，写的乱！！）

写的比较乱！！

创建一个JdbcUtils类

在里面创建方法

获取Connection对象

//获取Connection对象
public Connection getConnection(String dbName, String dbUser, String dbPwd) {
    Connection connection = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(
                "jdbc:mysql://192.168.153.141:3306/"+dbName,
                dbUser, dbPwd);
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return connection;
}

获取PreparedStatement

public PreparedStatement getStatement(Connection connection, String sql) {
    PreparedStatement preparedStatement = null;
    try {
        preparedStatement = connection.prepareStatement(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return preparedStatement;
}

释放资源

public void close(Connection connection){
    this.close(null,null,connection);
}
public void close(PreparedStatement psmt,Connection connection){
    this.close(null,psmt,connection);
}
public void close(ResultSet rs,PreparedStatement psmt,Connection connection){
    try {
        if (rs != null) {
            rs.close();
        }
        if (psmt != null){
            psmt.close();
        }
        if (connection != null){
            connection.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

精简操作

创建datebase.properties

mysqldriver=com.mysql.cj.jdbc.Driver
mysqlurl=jdbc:mysql://192.168.153.141:3306/jdbcstudb
mysqluser=root
mysqlpwd=root
oracleDriver=oracle.jdbc.driver.OracleDriver
oracleUrl=jdbc:oracle:thin:@192.168.153.144:1521:prod
oracleuesr=cp
oraclepwd=oracle

增删改

创建接口

public interface Dogdao {
    Integer saveDog(Dog dog);

    Integer updateDog(Dog dog);

    Integer delById(Integer id);

    Integer delByHealth(Integer health);



}

创建类DogDaoImpl

public class DogDaoImpl extends BaseDao implements Dogdao{
    @Override
    public Integer saveDog(Dog dog) {
        Connection connection = super.getConnection();
        String sql = "insert into dog(name,health,love,strain,lytm) value(?,?,?,?,now())";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,dog.getName());
            preparedStatement.setInt(2,dog.getHealth());
            preparedStatement.setInt(3,dog.getLove());
            preparedStatement.setString(4,dog.getStrain());
            int num = preparedStatement.executeUpdate();
            if (num > 0){
                System.out.println("新增成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.close(connection);
        }
        return 0;
    }
    @Override
    public Integer updateDog(Dog dog) {
        Connection connection = super.getConnection();
        String sql = "update dog set name = ? where id = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,dog.getName());
            preparedStatement.setInt(2,dog.getId());
            int num = preparedStatement.executeUpdate();
            if (num > 0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.close(connection);
        }
        return 0;
    }
    @Override
    public Integer delById(Integer id) {
        Connection connection = super.getConnection();
        String sql = "delete from dog where id = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            int num = preparedStatement.executeUpdate();
            if (num > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.close(connection);
        }
        return 0;
    }
    @Override
    public Integer delByHealth(Integer health) {
        Connection connection = super.getConnection();
        String sql = "delete from dog where health = ?";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,health);
            int num = preparedStatement.executeUpdate();
            if (num > 0){
                System.out.println("删除成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            super.close(connection);
        }
        return 0;
    }

测试类

public class AppTest 
{
    @Test
    public void testDogDaoInsert(){
        Dogdao dogDao = new DogDaoImpl();
        Dog dog = new Dog("芬达", 98, 88, "中华田园狗");
        dogDao.saveDog(dog);
    }

    @Test
    public void testDogDaoUpdate(){
        Dogdao dogDao = new DogDaoImpl();
        Dog dog = new Dog("小只因", 100, 88, "中华田园狗");
        dog.setId(5);
        dogDao.updateDog(dog);
    }

    @Test
    public void testDogDaoDeleteById(){
        Dogdao dogDao = new DogDaoImpl();
        dogDao.delById(3);
    }

    @Test
    public void testDogDaoDeleteByHealth(){
        Dogdao dogDao = new DogDaoImpl();
        dogDao.delByHealth(98);
    }

新增删除测试结果如下：

精简写法

创建一个BaseDao类
将通用的部分卸载一个类里，调用即可

public class BaseDao {
    private static String driver;
    private static String url;
    private static String user;
    private static String pwd;

    static {
        Properties properties = new Properties();
        InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream("datebase.properties");
        try {
            properties.load(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = properties.getProperty("mysqldriver");
        url = properties.getProperty("mysqlurl");
        user = properties.getProperty("mysqluser");
        pwd = properties.getProperty("mysqlpwd");
        System.out.println(driver);
        System.out.println(url);
        System.out.println(user);
        System.out.println(pwd);
    }

    public Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    public void close(Connection connection){
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close(PreparedStatement preparedStatement,Connection connection){
        try {
            if (connection != null) {
                connection.close();
            }
            if (preparedStatement != null){
                preparedStatement.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     *讲新增，修改，删除，同意抽象到一个方法中
     * @param sqlStr
     * @param params ...用法：表示n哥参数[0,+无穷]
     * @return
     */
    public int executeUpdate(String sqlStr,Object... params){
        Connection connection = this.getConnection();
        PreparedStatement preparedStatement = null;
        String sql = sqlStr;
        int num = -1;
        try{
            preparedStatement = connection.prepareStatement(sql);
            if (null != params){
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            num = preparedStatement.executeUpdate();

        } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        this.close(preparedStatement,connection);
    }
        return num;

    }

新增的精简写法

public Integer saveDog(Dog dog) {

        String sql = "insert into dog(name,health,love,strain,lytm) value(?,?,?,?,now())";
        int num = super.executeUpdate(sql, dog.getName(), dog.getHealth(), dog.getLove(), dog.getStrain());
        return num;
}

修改

public Integer updateDog(Dog dog) {

        String sql = "update dog set name = ? where id = ?";
        int num = super.executeUpdate(sql, dog.getName(), dog.getId());
        if (num > 0){
            System.out.println("修改成功");
        }
        return num;

删除

public Integer delById(Integer id) {

        String sql = "delete from dog where id = ?";
        int num = super.executeUpdate(sql,id);
        if (num > 0 ){
            System.out.println("删除成功");
        }
        return num;