一个简单的JDBC优化实例

前言

记录了自己通过学习JDBC后自己写的一个实例并进行优化

过程

使用MySQL数据库和MyEclipse IDE

1. 首先建立好一个数据库用于测试

新建了一个名为db_test的数据库，为了更好支持中文，字符集设置为utf-8。

新建一张表，用户表tbl_users

用户表

CREATE TABLE `tbl_users` (
`u_id`  int(10) NOT NULL ,
`u_name`  varchar(255) NULL ,
`u_email`  varchar(255) NULL ,
`u_balance`  double(10,2) NULL ,
PRIMARY KEY (`u_id`)
)
;

2. 建立Web项目

1. 在src目录里新建properties文件

右键 New –> File
命名为db_config.properties

source属性里面如下：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db_test
user=root
password=

记得导入数据库连接驱动的Jar包

2. 建立util工具包

建立连接工厂类

/**
 * 工具包
 */
package com.lidengju.util;

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

/**
 * @author KevinLee
 * @version 2016年3月13日 下午4:30:22
 */
public class ConnecterFactory {
    private String name;
    private static String db_driver;
    private static String db_url;
    private static String db_user;
    private static String db_password;

    private static final ConnecterFactory cFactory = new ConnecterFactory();
    private Connection conn;

    static{
        Properties prop=new Properties();
        try {
            InputStream inS
            =ConnecterFactory.class.getClassLoader().getResourceAsStream("db_config.properties");// 以流方式得到文件
            prop.load(inS);// 载入配置
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println("找不到db_config.properties");
        }
        db_driver=prop.getProperty("driver");
        db_url=prop.getProperty("url");
        db_user=prop.getProperty("user");
        db_password=prop.getProperty("password");
    }

    /**
     * 私有构造函数
     */
    private ConnecterFactory(){

    }

    /**
     * @return 得到一个连接工厂实例
     */
    public static ConnecterFactory getInstance(){
        return cFactory;
    }

    /**
     * @return 建立一个连接
     */
    public Connection makeConnection() {
        try {
            Class.forName(db_driver);
        } catch (ClassNotFoundException e) {
            System.out.println("数据库驱动异常");
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(db_url, db_user, db_password);
        } catch (SQLException e) {
            System.out.println("建立连接发生异常");
            e.printStackTrace();
        }
        return conn;
    }
}

2. 建立Entity实体包

建立ID的实体

package com.lidengju.entity;

public class IdEntity {
    protected int u_id;// 保护类型，子类可以访问

    public int getU_id() {
        return u_id;
    }

    public void setU_id(int u_id) {
        this.u_id = u_id;
    }

}

建立User的实体并继承ID实体

/**
 * 
 */
package com.lidengju.entity;


/**
 * @author KevinLee
 * @version 2016年3月13日 下午5:48:01
 */
public class User extends IdEntity {
    private String u_name;// 用户姓名
    private String u_email;// 用户邮箱
    private Double u_balance;// 用户余额

    public String getU_name() {
        return u_name;
    }
    public void setU_name(String u_name) {
        this.u_name = u_name;
    }
    public String getU_email() {
        return u_email;
    }
    public void setU_email(String u_email) {
        this.u_email = u_email;
    }

    public Double getU_balance() {
        return u_balance;
    }
    public void setU_balance(Double u_balance) {
        this.u_balance = u_balance;
}

    @Override
    public String toString() {
        // TODO Auto-generated method stub
        // 用于查看实体的内容
        return "User-[ID:"+u_id+" Name:"+u_name+" Email:"+u_email;
    }
}

4. 建立数据库操作包DAO

package com.lidengju.dao;

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

import com.lidengju.entity.User;

public interface UserDao {
    // 定义几种操作

    /**新增一个用户
     * @param conn 数据库连接
     * @param user 用户实体
     * @throws SQLException
     */
    public void save(Connection conn, User user) throws SQLException;

    /**修改一个用户
     * @param conn 数据库连接
     * @param id 更新用户的ID
     * @param user 更新后的内容
     * @throws SQLException
     */
    public void update(Connection conn, int id, User user) throws SQLException;

    /**删除一个用户
     * @param conn 数据库连接 
     * @param user 要删除的用户实体
     * @throws SQLException
     */
    public void delete(Connection conn, User user) throws SQLException;

    /**获得一个用户
     * @param _id 用户ID
     * @return
     */
    public User getUserById(Connection conn, int id) throws SQLException;

    /**用户充值
     * @param conn 数据库连接
     * @param id 要充值的用户ID
     * @param money 充值的数值
     * @throws SQLException
     */
    public void charge(Connection conn, User user, double money) throws SQLException;

    /**
     * 转账
     * @param conn 数据库连接
     * @param outId  转出用户
     * @param inId 接收用户
     * @throws SQLException
     */
    public void transfer(Connection conn,int outId,int inId,double money) throws SQLException;

}

5. 建立数据库操作实现DaoImpl

实现UserDao接口

package com.lidengju.daoimpl;

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

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

public class UserDaoImpl implements UserDao {

    @Override
    public void save(Connection conn, User user) throws SQLException {
        // TODO Auto-generated method stub
        String sql = "INSERT INTO tbl_users(u_id,u_name,u_email,u_balance) values (?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, user.getU_id());
        ps.setString(2, user.getU_name());
        ps.setString(3, user.getU_email());
        ps.setDouble(4, user.getU_balance());
        ps.execute();
        System.out.println("新增用户"+user.getU_id()+"成功");
    }

    @Override
    public void update(Connection conn, int id, User user) throws SQLException {
        // TODO Auto-generated method stub
        String sql = "UPDATE tbl_users SET u_name=?,u_email=? WHERE u_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, user.getU_name());
        ps.setString(2, user.getU_email());
        ps.setLong(3, user.getU_id());
        ps.execute();
        System.out.println("更新用户"+user.getU_id()+"成功");
    }

    @Override
    public void delete(Connection conn, User user) throws SQLException {
        // TODO Auto-generated method stub
        String sql = "DELETE FROM tbl_users WHERE u_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setLong(1, user.getU_id());
        ps.execute();
        System.out.println("删除用户"+user.getU_id()+"成功");
    }

    @Override
    public User getUserById(Connection conn, int id) throws SQLException {
        // TODO Auto-generated method stub
        User user = new User();
        String sql = "SELECT * FROM tbl_users WHERE u_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            user.setU_id(rs.getInt("u_id"));
            user.setU_name(rs.getString("u_name"));
            user.setU_email(rs.getString("u_email"));
            user.setU_balance(rs.getDouble("u_balance"));
        }
        return user;
    }

    @Override
    public void charge(Connection conn, User user, double money)
            throws SQLException {
        // TODO Auto-generated method stub
        String sql = "UPDATE tbl_users SET u_balance=? WHERE u_id=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setDouble(1, user.getU_balance() + money);
        ps.setLong(2, user.getU_id());
        ps.execute();
        System.out.println("为用户"+user.getU_id()+"成功充值"+money);
    }

    @Override
    public void transfer(Connection conn, int outId, int inId, double money)
            throws SQLException {
        // TODO Auto-generated method stub

        try {
            User outUser = getUserById(conn, outId);
            User inUser=getUserById(conn, inId);
            if (outUser.getU_balance() - money < 0) {
                System.out.println("余额不足无法转账");
                return;
            }
            String sqlOut = "UPDATE tbl_users SET u_balance=? WHERE u_id=?";
            PreparedStatement psOut = conn.prepareStatement(sqlOut);
            psOut.setDouble(1, outUser.getU_balance() - money);
            psOut.setLong(2, outId);
            psOut.execute();
            String sqlIn = "UPDATE tbl_users SET u_balance=? WHERE u_id=?";
            PreparedStatement psIn = conn.prepareStatement(sqlIn);
            psIn.setDouble(1, inUser.getU_balance() + money);
            psIn.setLong(2, inId);
            psIn.execute();

            System.out.println("用户"+outUser.getU_id()+"成功为用户"+inUser.getU_id()+"转账"+money);
        } catch (Exception e) {
            // TODO: handle exception
        }
    }

}

6. 建立测试包

建立UserDao测试类

package com.lidengju.test;

import java.sql.Connection;

import com.lidengju.dao.UserDao;
import com.lidengju.daoimpl.UserDaoImpl;
import com.lidengju.entity.User;
import com.lidengju.util.ConnecterFactory;

public class UserDaoTest {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        try {
            conn=ConnecterFactory.getInstance().makeConnection();
            UserDao userDao=new UserDaoImpl();
            // 用户1
            User u1=new User();
            u1.setU_id(1001);
            u1.setU_name("Kevin");
            u1.setU_email("hi@lidengju.com");
            u1.setU_balance(1000d);
            // 用户2
            User u2=new User();
            u2.setU_id(1002);
            u2.setU_name("Bob");
            u2.setU_email("null");
            u2.setU_balance(200d);

            userDao.save(conn, u1);
            userDao.save(conn, u2);
            System.out.println(u1.toString());
            System.out.println(u2.toString());

            u2.setU_email("hi@lidengju.com");
            userDao.update(conn, 1002, u2);

            //为用户2充值200
            userDao.charge(conn, u2, 200d);
            u2=userDao.getUserById(conn, 1002);
            u2.toString();

            userDao.transfer(conn, 1002, 1001, 500D);//用户2为用户1转账，期望：失败
            userDao.transfer(conn, 1001, 1002, 500D);//用户1为用户2转账，期望：成功
            //最终用户1余额为：500，用户2余额为：900

            User u3=new User();
            u3.setU_id(1003);
            u3.setU_name("Test");
            u3.setU_email("null");
            u3.setU_balance(100d);
            userDao.save(conn, u3);

            //userDao.delete(conn, u3);
        } catch (Exception e) {
            // TODO: handle exception
        }
    }

}

数据库结果