JDBC编程之优化

1、创建 dbconfig.properties

driver=com.mysql.jdbc.Driver
dburl=jdbc:mysql://localhost:3306/mytest
user=root
password=

2、创建 ConnectionFactory.java

package com.test.util;

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

public class ConnectionFactory {

    private static String driver;
    private static String dburl;
    private static String user;
    private static String password;
    
    private static final ConnectionFactory factory = new ConnectionFactory();
    
    private Connection conn;
    
    static{
        Properties prop = new Properties();
        try{
            InputStream in = ConnectionFactory.class.getClassLoader()
                    .getResourceAsStream("dbconfig.properties");
            prop.load(in);
        }catch(Exception e){
            System.out.println("配置文件读取错误");
        }
        driver = prop.getProperty("driver");
        dburl = prop.getProperty("dburl");
        user = prop.getProperty("user");
        password = prop.getProperty("password");
        
    }
    private ConnectionFactory(){
        
    }
    public static ConnectionFactory getInstance(){
        return factory;
    }
    public Connection makeConnection(){
        try{
            Class.forName(driver);
            conn = DriverManager.getConnection(dburl,user,password);
        }catch(Exception e){
            e.printStackTrace();
        }
        return conn;
    }
}

3、抽象类 IdEntity.java

package com.test.entity;

public abstract class IdEntity {
 
    protected int id;
    public int getId(){
        return id;
    }
    public void setId(int id){
        this.id = id;
    }
}

4、User.java 继承抽象类IdEntity.java

package com.test.entity;

public class User extends IdEntity {
    private String name;
    private String password;
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    @Override
    public String toString() {
        return "User [name=" + name + ", password=" + password + ", id=" + id
                + "]";
    }
    
    
    
}

5、Address.java 继承抽象类IdEntity.java

package com.test.entity;

public class Address extends IdEntity{

    private String address;
    private int userid;
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public int getUserid() {
        return userid;
    }
    public void setUserid(int userid) {
        this.userid = userid;
    }
    @Override
    public String toString() {
        return "Address [address=" + address + ", userid=" + userid + ", id="
                + id + "]";
    }
    
    
}

6、UserDao.java 定义接口

package com.test.dao;

import java.sql.Connection;
import java.sql.SQLException;

import com.test.entity.User;

public interface UserDao {

    public void save(Connection conn,User user)throws SQLException;
    
    public void update(Connection conn,int id,User user) throws SQLException;
    
    public void delete(Connection conn,User user)throws SQLException;
    
}

7、实现UserDao接口，UserDaoImpl.java

package com.test.dao.impl;

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

import com.test.dao.UserDao;
import com.test.entity.User;

public class UserDaoImpl implements UserDao{

    @Override
    public void save(Connection conn, User user) throws SQLException {
        PreparedStatement ps = conn.prepareCall("insert into person(name,password) values(?,?)");
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.execute();
    }

    @Override
    public void update(Connection conn, int id, User user) throws SQLException {
        String updateSql ="update person set name= ?,password=? where id =?";
        PreparedStatement ps = conn.prepareStatement(updateSql);
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setInt(3, id);
        ps.execute();
    }

    @Override
    public void delete(Connection conn, User user) throws SQLException {
        PreparedStatement ps = conn.prepareStatement("delete from uesr where id = ?");
        ps.setInt(1, user.getId());
        ps.execute();
    }

}

8、测试类 UserDaoTest.java

package com.test.testclass;

import java.sql.Connection;
import java.sql.SQLException;

import com.test.dao.UserDao;
import com.test.dao.impl.UserDaoImpl;
import com.test.entity.User;
import com.test.util.ConnectionFactory;

public class UserDaoTest {

    public static void main(String[] args) {
        Connection conn = null;
        
        
        try {
            conn = ConnectionFactory.getInstance().makeConnection();
            conn.setAutoCommit(false);
            UserDao userDao = new UserDaoImpl();
            User tom = new User();
            tom.setName("tom");
            tom.setPassword("123456");
            userDao.save(conn, tom);
            conn.commit();
        } catch (SQLException e) {
            try{
                conn.rollback();
            }catch(Exception e2){
                e2.printStackTrace();
            }
            e.printStackTrace();
        }
        
    }
}