#配置数据库数据源
package com.itang.utils;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DBCPUtil {
private static DataSource dataSource;
static{
try {
InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties props = new Properties();
props.load(in);
dataSource = BasicDataSourceFactory.createDataSource(props);
} catch (Exception e) {
e.printStackTrace();
throw new ExceptionInInitializerError(e);
}
}
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
try {
return dataSource.getConnection();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* 功能:关闭数据库连接资源
* @param rs
* ResultSet对象
* @param st
* Statement 对象
* @param conn
* Connection对象
*/
public static void closeSource(ResultSet rs, Statement st, Connection conn)
{
if(rs!=null)
{
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
closeSource(st,conn);
}
}
}
public static void closeSource(Statement st, Connection conn) {
if(st!=null)
{
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
if(conn!=null)
{
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
}
获取配置文件的属性值
public class GetDefaultValue {
private static String defaultimagepath;
private static String defaultimagename;
static {
ResourceBundle bundle=ResourceBundle.getBundle("defaultvalueconfig");
defaultimagepath = bundle.getString("defaultimagepath");
defaultimagename = bundle.getString("defaultimagename");
}
public static String getDefaultimagepath()
{
return defaultimagepath;
}
public static String getDefaultimagename()
{
return defaultimagename;
}
}
利用common-dbutils.jar和common-dbcp.jar工具来操作数据库:
注意查询的时候:
QueryRunner qr = new QueryRunner(dataSource);
qr.query(sql, new BeanHandler<User>(User.class));查询对象
qr.query(sql, new BeanListHandler<User>(User.class));查询对象列表
qr.query(sql, new ScalarHandler<>());返回单个值
具体可以参考下面代码:
package com.itwang.dao.impl;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.itwang.dao.InterestNumberDao;
import com.itwang.domain.InterestCard;
import com.itwang.domain.InterestNumber;
import com.itwang.utils.DBCPUtil;
public class InterestNumberDaoImpl implements InterestNumberDao {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
@Override
public void save(InterestNumber interestNumber) {
// TODO Auto-generated method stub
try {
qr.update("insert into interestnumber(id,name,follownumber,description,path,filename) values(?,?,?,?,?,?)",interestNumber.getId(),interestNumber.getName(),interestNumber.getFollownumber(),interestNumber.getDescription(),interestNumber.getPath(),interestNumber.getFilename());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public List<InterestNumber> findAllInterestNumber() {
// TODO Auto-generated method stub
List<InterestNumber> list=null;
try {
list=qr.query("select * from interestNumber",new BeanListHandler<InterestNumber>(InterestNumber.class));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public void deleteInterestNumber(String id) {
// TODO Auto-generated method stub
try {
qr.update("delete from interestnumber where id=?", id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public InterestNumber findInterestNumberById(String id) {
// TODO Auto-generated method stub
InterestNumber interestNumber= null;
try {
interestNumber=qr.query("select * from interestnumber where id=?", new BeanHandler<InterestNumber>(InterestNumber.class), id);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return interestNumber;
}
@Override
public void updateInterestNumber(InterestNumber interestNumber) {
// TODO Auto-generated method stub
try {
qr.update("update interestnumber set name=?,follownumber=?,description=?,path=?,filename=? where id=?",interestNumber.getName(),interestNumber.getFollownumber(),interestNumber.getDescription(),interestNumber.getPath(),interestNumber.getFilename(),interestNumber.getId());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public int checkStatusInterestNumber(String id, String interestnumberid) {
// TODO Auto-generated method stub
Long count=null;
try {
count=qr.query("select count(*) from interestrelative where uid=? and pid=?",new ScalarHandler<>(), id,interestnumberid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(count.intValue()>0)
return 1;
else
return 0;
}
@Override
public void deleteRelative(String uid, String pid) {
// TODO Auto-generated method stub
try {
qr.update("delete from interestrelative where uid=? and pid=?", uid,pid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public void addRelative(String id,String uid, String pid) {
// TODO Auto-generated method stub
try {
qr.update("insert into interestrelative(id,uid,pid) values(?,?,?)",id, uid,pid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public List<InterestNumber> findInterestNumberByKeywords(String searchKeywords) {
// TODO Auto-generated method stub
List<InterestNumber> list=null;
String sqlstring="select * from interestnumber where name like CONCAT('%', ?, '%')";
try {
list=qr.query(sqlstring,new BeanListHandler<InterestNumber>(InterestNumber.class),searchKeywords );
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
@Override
public void updateFollowNumber(int totalUser,String pid) {
// TODO Auto-generated method stub
try {
qr.update("update interestnumber set follownumber=? where id=?",totalUser,pid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Override
public int getTotalUserByPid(String pid) {
// TODO Auto-generated method stub
Long total=null;
try {
total=qr.query("select count(*) from interestrelative where pid=?", new ScalarHandler<>(), pid);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return total.intValue();
}
@Override
public void addInteretCard(InterestCard interestCard) {
// TODO Auto-generated method stub
try {
qr.update("insert into interestcard(id,datetime,title,content,uid,pid) values(?,?,?,?,?,?)",interestCard.getId(),interestCard.getDatetime(),interestCard.getTitle(),interestCard.getContent(),interestCard.getUser().getId(),interestCard.getInterestnumber().getId());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}