java 配置文件设计,java通过加载配置文件设计数据库

在设计数据库的连接修改删除等操作时，一般我们都是将数据库的配置信息在代码中设置。这里我们通过加载配置文件的方式来设计数据库的操作。

首先编写配置文件，在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;

}

}