前言
记录了自己通过学习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
}
}
}