这是我写的连接数据库(mysql) 对连接数据库,采用了特殊的方法,还有 对数据库的增删该查的操作 将其封装起来。方便以后的调用。
/*******************************************************************
文件夹名称:com.jdbc
文件名称:DbConn.java
系统名称:jtlcWeb
模块名称:
功能说明:
系统版本:1.0
开发人员: lsit
开发时间:2012-9-7下午12:38:27
相关文档:TODO
***************************************************************** */
package com.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.util.Vector;
/*
*
*
*
* 数据库连接
*/
public class DbConn
{
// 连接数据库的用户名
private String username;
// 连接数据库的密码
private String password;
// 连接数据库的url
private String url;
// 连接数据库的驱动
private String driver;
// 连接池,用来存放连接的
private Vector<Connection> pool;
// 连接池的大小 ,换句话说就是连接池中(pool)存入连接的数量
private int poolSize = 1;
private static DbConn instance = null;
public DbConn()
{
init();
}
/**
* 初始化连接池
*/
private void init()
{
pool = new Vector<Connection>();
readConfig();
addConnction();
// TODO Auto-generated method stub
}
/*
* 提供返回本类实例的一个接口
*/
public static DbConn getInstance()
{
if (null == instance)
{
instance = new DbConn();
}
return instance;
}
/**
* 向连接池添加连接
*/
private void addConnction()
{
try
{
Class.forName(this.driver);
Connection connection = null;
for (int i = 0; i < poolSize; i++)
{
connection = DriverManager.getConnection(url, username,
password);
pool.add(connection);
}
} catch (ClassNotFoundException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 从连接池中取出一个连接
*/
public synchronized Connection getConnection()
{
if (pool.size() > 0)
{
Connection connection = pool.get(0);
pool.remove(connection);
return connection;
} else
{
return null;
}
}
/**
* 将连接放回连接池
*/
public synchronized void reasle(Connection connection)
{
pool.add(connection);
}
/*
* 读取配置文件
*/
public void readConfig()
{
Properties properties = new Properties();
InputStream iStream = this.getClass().getResourceAsStream(
"pool.properties");
try
{
properties.load(iStream);
this.username = properties.getProperty("username");
this.password = properties.getProperty("password");
this.driver = properties.getProperty("driver");
this.url = properties.getProperty("url");
this.poolSize = Integer
.parseInt(properties.getProperty("poolSize"));
} catch (IOException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
package com.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*
*
*/
public class DbUtils
{
private Connection conn;
private PreparedStatement stat;
private ResultSet rs;
private static DbUtils instance;
public DbUtils()
{
}
/**
* 返回本类的一个实例
*/
public static DbUtils getInstance()
{
if (null == instance)
{
instance = new DbUtils();
}
return instance;
}
/*
*
*/
public boolean save(String sql)
{
boolean isOk = false;
try
{
conn = DbConn.getInstance().getConnection();
stat = conn.prepareStatement(sql);
stat.execute();
isOk = true;
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return isOk;
}
/*
*
*
*/
public boolean update(String sql)
{
boolean isOk = false;
conn = DbConn.getInstance().getConnection();
try
{
stat = conn.prepareStatement(sql);
stat.executeUpdate(sql);
isOk = true;
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return isOk;
}
/*
*
*/
public ResultSet query(String sql)
{
rs = null;
try
{
conn = DbConn.getInstance().getConnection();
stat = conn.prepareStatement(sql);
rs = stat.executeQuery();
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
/*
*
*/
public boolean delete(String sql)
{
boolean isOk = false;
conn = DbConn.getInstance().getConnection();
try
{
stat = conn.prepareStatement(sql);
stat.executeUpdate(sql);
isOk = true;
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
return isOk;
}
/**
* 分页查询
*
* @param page
* 显示第几页
* @param pageSize
* 一页显示多少条记录
* @param sql
* 要执行的select 语句
* @return ResultSet 分页查询的结果
* @author Administrator
*/
public ResultSet queryByPage(int page, int pageSize, String sql)
{
rs = null;
try
{
int begin = (page - 1) * pageSize;
sql = sql + " limit " + begin + "," + pageSize;
rs = query(sql);
} catch (Exception e)
{
e.printStackTrace();
}
return rs;
}
/**
* 获取总记录数
*
* @param sql
* 要执行的select 语句
* @return int 总记录数
*/
public int getRecordCount(String sql)
{
int count = 0;
try
{
sql = "select count(*) " + sql.substring(sql.indexOf("from"));
rs = query(sql);
rs.next();
count = rs.getInt(1);
} catch (Exception e)
{
e.printStackTrace();
} finally
{
try
{
rs.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
return count;
}
/**
* 获取总页数
*
* @param sql
* 要执行的select语句
* @param pageSize
* 一页显示的记录数
* @return int 总页数
*/
public int getPageCount(String sql, int pageSize)
{
int count = 0;
int total = this.getRecordCount(sql);
count = total % pageSize == 0 ? (total / pageSize)
: (total / pageSize + 1);
return count;
}
/**
* 释放连接 关闭statement
*/
public void close()
{
try
{
if (rs != null)
{
rs.close();
}
if (stat != null)
{
stat.close();
}
if (conn != null)
{
conn.close();
}
} catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}