我写的一个db操作方法如下:
package com.xfrb.common;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;public class Dbclass {
Connection con=null;
Statement st = null;
ResultSet rs = null;
DataSource ds=null;
PreparedStatement prepstmt = null;
boolean flag = true;
/**
*获取数据源
*/
public Dbclass() throws Exception
{
try{
Context Ctx=new InitialContext();
if (Ctx==null){
throw new Exception("不能创建连接池");
}else{
ds=(DataSource)Ctx.lookup("java:comp/env/jdbc/sqlserverPool");
}
}catch(Exception e){
Exception ex = new Exception("获得数据源期间出现问题:"+e.getMessage());
throw ex;
}
}
/**
*创建connection,将自动提交设为假
*/
public void createConnection() throws SQLException
{
try{
con=ds.getConnection();
con.setAutoCommit(false);
}catch(SQLException e){
SQLException ex = new SQLException("创建连接期间出现问题:"+e.getMessage());
throw ex;
}
} /**
* 创建PreparedStatement
* @param sql String sql语句
*/
public void createPrepareStatement(String sql) throws SQLException {
try{
prepstmt = con.prepareStatement(sql);
}catch(SQLException e){
SQLException ex = new SQLException("创建prepareStatement期间出现问题:"+e.getMessage());
throw ex;
}
}
/**
* 设置对应值
* @param index 参数索引
* @param value 对应值
*/
public void setString(int index,String value) throws SQLException {
prepstmt.setString(index,value);
}
public void setInt(int index,int value) throws SQLException {
prepstmt.setInt(index,value);
}
public void setBoolean(int index,boolean value) throws SQLException {
prepstmt.setBoolean(index,value);
}
public void setDate(int index,Date value) throws SQLException {
prepstmt.setDate(index,value);
}
public void setLong(int index,long value) throws SQLException {
prepstmt.setLong(index,value);
}
public void setFloat(int index,float value) throws SQLException {
prepstmt.setFloat(index,value);
}
public void clearParameters() throws SQLException
{
prepstmt.clearParameters();
}
/**
* 返回预设状态
*/
public PreparedStatement getPreparedStatement() {
return prepstmt;
}
/**
* prepstmt方式插入/修改
* @return i int 响应的记录条数
*/
public int executeUpdate() throws SQLException {
int i=0;
if (prepstmt != null){
i=prepstmt.executeUpdate();
}
return i;
}/**
* prepstmt方式查询返回ResultSet
* @return ResultSet 记录集
*/
public ResultSet executeQuery() throws SQLException {
return prepstmt.executeQuery();
}/**
*提交
*/
public void comit() throws SQLException
{
try {
flag = false;
con.commit();
} catch (SQLException e) {
SQLException ex = new SQLException("提交事务期间出现问题:"+e.getMessage());
throw ex;
}
}
/**
* 回滚
*/
public void rollback() throws SQLException
{
try {
flag = false;
con.rollback();
} catch (SQLException e) {
SQLException ex = new SQLException("回滚事务期间出现问题:"+e.getMessage());
throw ex;
} }/**
*关闭ResultSet对象
*@param rs ResultSet 记录集
*/
public void closeResultSet(ResultSet rs){ try{
if(rs!=null){
rs.close();
rs=null;
}
}catch(Exception e){
e.printStackTrace();
} }
/**
* 关闭Prepstmt对象
* @param prepstmt 预置对象
*/
public void closePrepstmtSet(ResultSet prepstmt){ try{
if(prepstmt!=null){
prepstmt.close();
prepstmt = null;
}
}catch(Exception e){
e.printStackTrace();
} }
/**
* 提交,关闭连接及其他对象
*/
public void closeSession() throws SQLException
{
try {
if(flag){
if(con!=null)
{
con.commit();
con.close();
con=null;
}
}else
{
if(con!=null)
con.close();
}
} catch (SQLException e) {
SQLException ex = new SQLException("关闭连接及其他对象期间出现问题:"+e.getMessage());
throw ex;
} } 在使用其中的createPrepareStatement(),executeUpdate()方法做两个表的操作时出下如下提示:java.sql.SQLException: 创建prepareStatement期间出现问题:[Microsoft][SQLServer 20
00 Driver for JDBC]Can't start a cloned connection while in manual transaction m
ode.使用方法如下:
import com.xfrb.common.Dbclass;import java.sql.ResultSet;
import java.sql.SQLException;public class Dbtest {
public ResultSet validateDocentName()
{
ResultSet rs = null;
Dbclass db = null;
try {
db = new Dbclass();
String sqlnew="insert into Users (name,deptid,sex,userid,re) values(?,?,?,?,?) ";
db.createConnection();
db.createPrepareStatement(sqlnew);
db.setString(1, "ma9");
db.setString(2, "125");
db.setString(3, "1");
db.setString(4, "2269");
db.setString(5, "77");
int i=db.executeUpdate();
String sql="insert into Depart (departid,departname) values(?,?)";
db.createPrepareStatement(sql);
db.setString(1, "000005");
db.setString(2, "测试5");
int j=db.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally
{
try {
db.clearParameters();
db.closeSession();
} catch (SQLException e1) {
e1.printStackTrace();
}
} return rs;
}}
package com.xfrb.common;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;public class Dbclass {
Connection con=null;
Statement st = null;
ResultSet rs = null;
DataSource ds=null;
PreparedStatement prepstmt = null;
boolean flag = true;
/**
*获取数据源
*/
public Dbclass() throws Exception
{
try{
Context Ctx=new InitialContext();
if (Ctx==null){
throw new Exception("不能创建连接池");
}else{
ds=(DataSource)Ctx.lookup("java:comp/env/jdbc/sqlserverPool");
}
}catch(Exception e){
Exception ex = new Exception("获得数据源期间出现问题:"+e.getMessage());
throw ex;
}
}
/**
*创建connection,将自动提交设为假
*/
public void createConnection() throws SQLException
{
try{
con=ds.getConnection();
con.setAutoCommit(false);
}catch(SQLException e){
SQLException ex = new SQLException("创建连接期间出现问题:"+e.getMessage());
throw ex;
}
} /**
* 创建PreparedStatement
* @param sql String sql语句
*/
public void createPrepareStatement(String sql) throws SQLException {
try{
prepstmt = con.prepareStatement(sql);
}catch(SQLException e){
SQLException ex = new SQLException("创建prepareStatement期间出现问题:"+e.getMessage());
throw ex;
}
}
/**
* 设置对应值
* @param index 参数索引
* @param value 对应值
*/
public void setString(int index,String value) throws SQLException {
prepstmt.setString(index,value);
}
public void setInt(int index,int value) throws SQLException {
prepstmt.setInt(index,value);
}
public void setBoolean(int index,boolean value) throws SQLException {
prepstmt.setBoolean(index,value);
}
public void setDate(int index,Date value) throws SQLException {
prepstmt.setDate(index,value);
}
public void setLong(int index,long value) throws SQLException {
prepstmt.setLong(index,value);
}
public void setFloat(int index,float value) throws SQLException {
prepstmt.setFloat(index,value);
}
public void clearParameters() throws SQLException
{
prepstmt.clearParameters();
}
/**
* 返回预设状态
*/
public PreparedStatement getPreparedStatement() {
return prepstmt;
}
/**
* prepstmt方式插入/修改
* @return i int 响应的记录条数
*/
public int executeUpdate() throws SQLException {
int i=0;
if (prepstmt != null){
i=prepstmt.executeUpdate();
}
return i;
}/**
* prepstmt方式查询返回ResultSet
* @return ResultSet 记录集
*/
public ResultSet executeQuery() throws SQLException {
return prepstmt.executeQuery();
}/**
*提交
*/
public void comit() throws SQLException
{
try {
flag = false;
con.commit();
} catch (SQLException e) {
SQLException ex = new SQLException("提交事务期间出现问题:"+e.getMessage());
throw ex;
}
}
/**
* 回滚
*/
public void rollback() throws SQLException
{
try {
flag = false;
con.rollback();
} catch (SQLException e) {
SQLException ex = new SQLException("回滚事务期间出现问题:"+e.getMessage());
throw ex;
} }/**
*关闭ResultSet对象
*@param rs ResultSet 记录集
*/
public void closeResultSet(ResultSet rs){ try{
if(rs!=null){
rs.close();
rs=null;
}
}catch(Exception e){
e.printStackTrace();
} }
/**
* 关闭Prepstmt对象
* @param prepstmt 预置对象
*/
public void closePrepstmtSet(ResultSet prepstmt){ try{
if(prepstmt!=null){
prepstmt.close();
prepstmt = null;
}
}catch(Exception e){
e.printStackTrace();
} }
/**
* 提交,关闭连接及其他对象
*/
public void closeSession() throws SQLException
{
try {
if(flag){
if(con!=null)
{
con.commit();
con.close();
con=null;
}
}else
{
if(con!=null)
con.close();
}
} catch (SQLException e) {
SQLException ex = new SQLException("关闭连接及其他对象期间出现问题:"+e.getMessage());
throw ex;
} } 在使用其中的createPrepareStatement(),executeUpdate()方法做两个表的操作时出下如下提示:java.sql.SQLException: 创建prepareStatement期间出现问题:[Microsoft][SQLServer 20
00 Driver for JDBC]Can't start a cloned connection while in manual transaction m
ode.使用方法如下:
import com.xfrb.common.Dbclass;import java.sql.ResultSet;
import java.sql.SQLException;public class Dbtest {
public ResultSet validateDocentName()
{
ResultSet rs = null;
Dbclass db = null;
try {
db = new Dbclass();
String sqlnew="insert into Users (name,deptid,sex,userid,re) values(?,?,?,?,?) ";
db.createConnection();
db.createPrepareStatement(sqlnew);
db.setString(1, "ma9");
db.setString(2, "125");
db.setString(3, "1");
db.setString(4, "2269");
db.setString(5, "77");
int i=db.executeUpdate();
String sql="insert into Depart (departid,departname) values(?,?)";
db.createPrepareStatement(sql);
db.setString(1, "000005");
db.setString(2, "测试5");
int j=db.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally
{
try {
db.clearParameters();
db.closeSession();
} catch (SQLException e1) {
e1.printStackTrace();
}
} return rs;
}}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货