在设计数据库的连接修改删除等操作时,一般我们都是将数据库的配置信息在代码中设置。这里我们通过加载配置文件的方式来设计数据库的操作。
首先编写配置文件,在java工程下新建一个配置文件“dbcpconfig.properties”。将数据库的配置信息填写在里面。
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myqq
username=root
password=mysqladmin
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=UTF8
defaultAutoCommit=true
defaultTransactionIsolation=READ_UNCOMMITTED
然后加载该配置文件实现数据库的连接操作。我们设计一个DBPool.class类
package com.sdust.im.DataBase;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import com.mysql.jdbc.PreparedStatement;
import javax.sql.DataSource;
/**
* 使用数据库连接池加大响应速度
*
*/
public class DBPool {
private static DataSource ds;
private DBPool() {
}
static {
try {
InputStream in = DBPool.class.getClassLoader().getResourceAsStream(
"dbcpconfig.properties");//获得配置文件的数据流
Properties pro = new Properties();
pro.load(in);//加载配置文件
ds = BasicDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
Connection con = null;
try {
con = ds.getConnection();
} catch (SQLException e) {
System.out.println("获取数据库连接失败....");
e.printStackTrace();
}
return con;
}
public static void close(Connection con) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
最后设计数据库的操作类UserDao.class
package com.sdust.im.DataBase;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.print.attribute.standard.PresentationDirection;
import com.sdust.im.bean.User;
/**
* 数据库操作
*
*/
public class UserDao {
private UserDao() {
}
/**
* 查询账号是否存在
*
*/
public static boolean selectAccount(String account) {
String sql0 = "use myqq";
String sql1 = "select * from user where account=?";
Connection con = DBPool.getConnection();
try {
con.setAutoCommit(false);
PreparedStatement ps;
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
ps.setString(1, account);
ResultSet rs = ps.executeQuery();
return rs.first() ? true : false;
} catch (SQLException e) {
e.printStackTrace();
}
DBPool.close(con);
return false;
}
/**
* 向数据库中添加账户
*
*/
public static int insertInfo(User user) {
String sql0 = "use myqq";
String sql1 = "insert into user (account,name,photo,birthday,password,gender)"
+ " values(?,?,?,?,?,?)";
Connection con = DBPool.getConnection();
try {
con.setAutoCommit(false);
} catch (SQLException e2) {
e2.printStackTrace();
}
PreparedStatement ps;
try {
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
ps.setString(1, user.getAccount());
ps.setString(2, user.getUserName());
ps.setBytes(3, user.getPhoto());
System.out.println(user.getPhoto().length);
ps.setDate(4, new java.sql.Date(user.getBirthday().getTime()));
ps.setString(5, user.getPassword());
ps.setInt(6, user.getGender());
ps.executeUpdate();
con.commit();
} catch (SQLException e) {
try {
System.out.println("插入数据库异常,正在进行回滚..");
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
return getLastID(con);
}
/**
* 得到最后一次插入的值
*/
public static int getLastID(Connection con) {
String sql0 = "use myqq";
String sql1 = "select MAX(id) as ID from user";// 注意:使用MAX(ID) 必须加上 as
// id 翻译
PreparedStatement ps;
ResultSet rs;
int id = -1;
try {
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
rs = ps.executeQuery();
if (rs.first())
id = rs.getInt("id");
} catch (SQLException e) {
e.printStackTrace();
}
DBPool.close(con);
return id;
}
/**
* 进行登录的验证
*/
public static boolean login(User user) {
boolean isExisted = false;
String sql0 = "use myqq";
String sql1 = "select * from user where account=? and password=?";
Connection con = DBPool.getConnection();
PreparedStatement ps;
ResultSet rs;
try {
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
ps.setString(1, user.getAccount());
ps.setString(2, user.getPassword());
rs = ps.executeQuery();
if (rs.first()) {
isExisted = true;
// 为用户添加自己的id
user.setId(rs.getInt("id"));
user.setAccount(rs.getString("account"));
user.setBirthday(rs.getDate("birthday"));
user.setGender(rs.getInt("gender"));
user.setPassword(rs.getString("password"));
user.setUserName(rs.getString("name"));
user.setPhoto(rs.getBytes("photo"));
}
} catch (SQLException e) {
e.printStackTrace();
}
DBPool.close(con);
return isExisted;
}
/**
* 更新在线状态
*/
public static void updateIsOnline(int id, int isOnline) {
String sql0 = "use myqq";
String sql1 = "update user set isOnline=? where id=?";
Connection con = DBPool.getConnection();
try {
con.setAutoCommit(false);
} catch (SQLException e1) {
e1.printStackTrace();
}
PreparedStatement ps;
ResultSet rs;
try {
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
ps.setInt(1, isOnline);
ps.setInt(2, id);
ps.executeUpdate();
con.commit();
} catch (SQLException e) {
try {
System.out.println("数据库正在回滚....");
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
DBPool.close(con);
}
public static ArrayList selectFriendByAccountOrID(Object condition) {
ArrayList list = new ArrayList();
String sql0 = "use myqq";
String sql1 = "";
int conFlag = 0;// 默认是0 表示使用id查找 1为使用id
if (condition instanceof String) {
sql1 = "select * from user where account=?";
conFlag = 1;
} else if (condition instanceof Integer)
sql1 = "select * from user where id=?";
Connection con = DBPool.getConnection();
PreparedStatement ps;
ResultSet rs;
try {
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
if (conFlag == 1)
ps.setString(1, (String) condition);
else if (conFlag == 0)
ps.setInt(1, (Integer) condition);
rs = ps.executeQuery();
while (rs.next()) {
User friend = new User();
friend.setId(rs.getInt("id"));
friend.setAccount(rs.getString("account"));
friend.setBirthday(rs.getDate("birthday"));
friend.setGender(rs.getInt("gender"));
friend.setUserName(rs.getString("name"));
if (rs.getInt("isOnline") == 1)
friend.setIsOnline(true);
else
friend.setIsOnline(false);
friend.setPhoto(rs.getBytes("photo"));
friend.setLocation(rs.getString("location"));
list.add(friend);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBPool.close(con);
return list;
}
public static ArrayList selectFriendByMix(String[] mix) {
ArrayList list = new ArrayList();
String sql0 = "use myqq";
String sql1 = "select * "
+ "from user "
+ "where ((YEAR(CURDATE())-YEAR(birthday))-(RIGHT(CURDATE(),5)
+ "between ? and ? ";
Connection con = DBPool.getConnection();
PreparedStatement ps;
ResultSet rs;
try {
if (mix[3].equals("3"))
sql1 += "and gender=1 or gender=0";
else if (mix[3].equals("1"))
sql1 += "and gender=1";
else if (mix[3].equals("0"))
sql1 += "and gender=0";
ps = con.prepareStatement(sql0);
ps.execute();
ps = con.prepareStatement(sql1);
ps.setInt(1, Integer.parseInt(mix[1]));
ps.setInt(2, Integer.parseInt(mix[2]));
rs = ps.executeQuery();
while (rs.next()) {
User friend = new User();
friend.setId(rs.getInt("id"));
friend.setAccount(rs.getString("account"));
friend.setBirthday(rs.getDate("birthday"));
friend.setGender(rs.getInt("gender"));
friend.setUserName(rs.getString("name"));
if (rs.getInt("isOnline") == 1)
friend.setIsOnline(true);
else
friend.setIsOnline(false);
friend.setPhoto(rs.getBytes("photo"));
friend.setLocation(rs.getString("location"));
list.add(friend);
}
} catch (SQLException e) {
e.printStackTrace();
}
DBPool.close(con);
return list;
}
}