一个简单的JDBC优化实例

来源：互联网发布：淘宝买家怎么延长收货编辑：程序博客网时间：2024/05/16 09:40

前言

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

过程

使用MySQL数据库和MyEclipse IDE

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

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

在Navicat里新建数据库

新建一张表，用户表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.Driverurl=jdbc:mysql://localhost:3306/db_testuser=rootpassword=

记得导入数据库连接驱动的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        }    }}

数据库结果

0 0