Connection连接和PreparedStatement 操作数据库
链接数据库操作的代码
连接数据库的几种方式
private Connection conn;
private PreparedStatement pstmt;
public Database() {
this.conn = null;
this.pstmt = null;
}
通过访url建立连接
public Connection getConnectionByUrl() throws Exception {
Connection conn = null;
try {
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS ="
+ "(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(ADDRESS ="
+ "(PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA"
+ "=(SERVICE_NAME = test)))", "root", "root123");
} catch (Exception e) {
System.out.println("数据库链接异常");
throw e;
}
if(conn==null){
System.out.println("数据库链接异常");
}
return conn;
}
通过访问数据源建立连接
public Connection getConnection() throws Exception {
Connection conn = null;
try {
conn = DriverManager.getConnection("proxool.Develop2");
} catch (Exception e) {
System.out.println("数据库链接异常");
throw e;
}
if(conn==null){
System.out.println("数据库链接异常");
getConnection();
}
return conn;
}
通过JDBC直接建立连接
@SuppressWarnings("unused")
private Connection getJDBCConnection() throws Exception {
BaseBean baseBean = new BaseBean();
String dbDriver = baseBean.getJDBCDriver();
String dbUrl = baseBean.getDatabaseURL();
String userName = baseBean.getUserName();
String userPassword = baseBean.getUserPassword();
Class.forName(dbDriver);
return DriverManager.getConnection(dbUrl, userName, userPassword);
}
开启数据库连接
public boolean connect() throws Exception {
this.conn = this.getConnection();
return true;
}
操作数据库
查询
public ResultSet executeQuery(String sql) throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.executeQuery();
return this.pstmt.getResultSet();
}
条件查询(支持多个条件)
public ResultSet executeQuery(String sql, List<String> params)
throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
for (int i = 0; i < params.size(); i++) {
this.pstmt.setString(i+1, params.get(i));
}
return this.pstmt.executeQuery();
}
条件查询(1个条件)
public ResultSet executeQuery(String sql, String param1)
throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setString(1, param1);
this.pstmt.executeQuery();
return this.pstmt.getResultSet();
}
条件查询(两个条件)
public ResultSet executeQuery(String sql, String param1, String param2)
throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setString(1, param1);
this.pstmt.setString(2, param2);
this.pstmt.executeQuery();
return this.pstmt.getResultSet();
}
执行sql语句
public void executeInsert(String sql) throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.executeUpdate();
}
执行sql 注入参数
public void executeInsert(String sql, int param1, int param2) throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
this.pstmt.setInt(1, param1);
this.pstmt.setInt(2, param2);
this.pstmt.executeUpdate();
}
执行sql 多个参数
public void executeInsert(String sql, String... param) throws Exception {
this.pstmt = this.conn.prepareStatement(sql);
for (int i = 0; i < param.length; i++) {
this.pstmt.setString(i+1, param[i]);
}
this.pstmt.executeUpdate();
}
事务处理开始
public void beginTransaction() throws Exception {
this.conn.setAutoCommit(false);
}
事务处理结束
public void endTransaction() throws Exception {
this.conn.setAutoCommit(true);
}
提交处理
public void commit() throws SQLException {
this.conn.commit();
}
回滚(还原数据记录)
public void rollback() throws SQLException {
this.conn.rollback();
}
关闭数据库连接
public void close() throws Exception {
if (this.pstmt != null)
this.pstmt.close();
if (this.conn != null)
this.conn.close();
}
关闭PrepareStatement
public void closePstmt() throws Exception {
if (this.pstmt != null)
this.pstmt.close();
}
proxool.xml 配置
<?xml version="1.0" encoding="UTF-8"?>
<proxool-config>
<proxool>
<alias>Develop2</alias>
<driver-url>jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = dxyytdb)))</driver-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<driver-properties>
<property name="user" value="root" />
<property name="password" value="root123" />
</driver-properties>
<maximum-connection-count>50</maximum-connection-count>
<prototype-count>2</prototype-count>
<house-keeping-sleep-time>20000</house-keeping-sleep-time>
<house-keeping-test-sql>select SYSDATE FROM DUAL</house-keeping-test-sql>
<statistics>1m,1h,1d</statistics>
<statistics-log-level>ERROR</statistics-log-level>
</proxool>
</proxool-config>