数据库连接及操作所遇到的问题
数据库运用DBUTIS如何连接:
第一步:配置xml文件。
第二步:导入3个包,分别为c3p0-0.9.2-pre1.jar、commons-dbutils-1.4.jar、mchange-commons-0.2.jar。
第三步:编写DBUTIS类得到连接池。
Xml文件:
<c3p0-config>
<default-config>
<property name="automaticTestTable">con_test</property>
<property name="checkoutTimeout">30000</property>
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<named-config name="mysql">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/hotel</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">5</property>
<property name="maxPoolSize">30</property>
</named-config>
</c3p0-config>
mysql:连接池的名称。
driverClass= com.mysql.jdbc.Driver:设置驱动
jdbcUrl= jdbc:mysql://localhost:3306/hotel:建立连接
hotel:数据库名称。
DBUTIS类:package cn.class3g.web.utils;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DBManager_c3p0 {
private static ComboPooledDataSource ds = null;
static {
try {
ds = new ComboPooledDataSource("mysql");
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
Connection conn = ds.getConnection();
return conn;
}
public static DataSource getDataSource() {
return ds;
}
}
具体操作中遇到的问题:
1 在查询数据库中某条记录时,是根据id查询的在查春过程中可能会犯些错误例如:
错误:
public Record findId(Integer id) {
DataSource ds = DBManager_c3p0.getDataSource();
//执行
QueryRunner runner = new QueryRunner(ds);
Record rd=null;
String sql="select * from record where id=?";
try {rd =runner.query(sql, new BeanHandler(Record.class),rd.getId());
} catch (SQLException e) {
e.printStackTrace();
}
return rd;
}
正确:
public Record findId(Integer id) {
DataSource ds = DBManager_c3p0.getDataSource();
//执行
QueryRunner runner = new QueryRunner(ds);
Record rd=null;
String sql="select * from record where id=?";
try {
rd =runner.query(sql, new BeanHandler(Record.class),id);
} catch (SQLException e) {
e.printStackTrace();
}
return rd;
}
2 在执行修改时应先查询出某条记录然后在执行修改。
@Test
public void update(){
Record entity=rService.findId(2);
entity.setUserid(1);
entity.setRoomid(111);
rService.update(entity);
System.out.println(entity);
}
3 在登录时对用户和密码进行验证。执行查询得到的结果转义成javabean实体对象并返回该实体对象。然后再判断该实体对象是否为空如果为空则返回false,不为空返回true。
public boolean loginCheck(String name, String pass) {
boolean flag = false;
DataSource ds =DBManager_c3p0.getDataSource();
QueryRunner runner = new QueryRunner(ds);
String sql = "select * from user where name=? and pass=?";
Object[] params = {name,pass};
try {
User entity=runner.query(sql,new BeanHandler<User>(User.class),params);
if(entity!=null){
flag=true;
}
} catch (SQLException e) {
e.printStackTrace();
}
return flag;
}