JavaWeb学习之路（二）--- 数据库操作优化

新建一个类ConnectionFactory，封装Connection；
新建一个文件properties文件dbconfig.properties，存放数据库配置数据；
示例代码：

// ConnectionFactory.java

package com.package.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");

        System.out.println("=========" + driver + "========");
        System.out.println("=========" + dburl + "========");
        System.out.println("=========" + user + "========");
        System.out.println("=========" + 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;
    }
}

// dbconfig.properties

driver = com.mysql.jdbc.Driver
dburl = jdbc:mysql://localhost:3306/test // test为数据库名称
user = root
password = 

完整示例：数据库中有一个表（tab_user），使用模型通过接口的方式，操作数据库中的数据；

数据模型：

// IdEntity

package com.package.entity;

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

// user.java

package com.package.entity;

public class User extends IdEntity {

    private String name;

    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;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    private String password;
    private String email;

    @Override
    public String toString() {
        return "User [name=" + name + ", password=" + password + ", email=" + email + ", id=" + id + "]";
    }

}

接口文件：

// UserDao.java

package com.package.dao;

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

import com.package.entity.User;

public interface UserDao {

    public void save(Connection conn, User user) throws SQLException;

    public void update(Connection conn, Long id, User user) throws SQLException;

    public void delete(Connection conn, User user) throws SQLException;

    public ResultSet get(Connection conn, User user) throws SQLException;

}

// 接口实现文件：

// UserDaoImpl.java

package com.package.dao.impl;

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

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

public class UserDaoImpl implements UserDao {

    @Override
    public void save(Connection conn, User user) throws SQLException {
        // TODO Auto-generated method stub
        PreparedStatement ps = conn.prepareCall("INSERT INTO tbl_user(name, password, email) VALUES (?,?,?)");
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.execute();
    }

    @Override
    public void update(Connection conn, Long id, User user) throws SQLException {
        // TODO Auto-generated method stub
        String updateSql = "UPDATE tbl_user SET name=?, password=?, email=? WHERE id=?";
        PreparedStatement ps = conn.prepareStatement(updateSql);
        ps.setString(1, user.getName());
        ps.setString(2, user.getPassword());
        ps.setString(3, user.getEmail());
        ps.setLong(4, user.getId());
        ps.execute();
    }

    @Override
    public void delete(Connection conn, User user) throws SQLException {
        // TODO Auto-generated method stub
        PreparedStatement ps = conn.prepareStatement("DELETE FROM tbl_user WHERE id=?");
        ps.setLong(1, user.getId());
        ps.execute();
    }

执行：

package com.package.test;


import java.sql.Connection;

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

public class UserDaoTest {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        try {
            conn = ConnectionFactory.getInstance().makeConnection();
            conn.setAutoCommit(false);

            UserDao userDao = new UserDaoImpl();
            User tom = new User();
            tom.setName("Tom");
            tom.setPassword("12345");
            tom.setEmail("4342323432@qq.com");
            userDao.save(conn, tom);
            conn.commit();
        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (Exception e2) {

            }
        }   
    }
}