jdbc继续改进版
/**
* @author sign
* @describe 改进第三版
*
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.Map;
import java.util.TreeMap;
public class JDBCUtil {
Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
String url="jdbc:mysql://localhost:3306/theatermanagement";
String user="root";
String psw="root";
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public Connection getConn(){
try {
conn=DriverManager.getConnection(url, user, psw);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public int update(String sql){
try {
stmt=this.getConn().createStatement();
return stmt.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return -1;
}finally{
release();
}
}
public LinkedList<Map<String,Object>> query(String sql){
LinkedList<Map<String,Object>> list=new LinkedList<Map<String,Object>>();
try {
stmt=getConn().createStatement();
rs=stmt.executeQuery(sql);
ResultSetMetaData rsmd=rs.getMetaData();
int count=rsmd.getColumnCount();
while(rs.next()){
Map<String,Object> map=new TreeMap<String,Object>();
for (int i = 0; i < count; i++) {
String key=rsmd.getColumnName(i+1);
String keyy="";
if(key.equals("")){// 聚合函数bug
System.out.println("聚合函数bug"+key);
keyy="count";
Object value=rs.getObject(1);
map.put(keyy, value);
}else{
Object value=rs.getObject(key);
map.put(key, value);
}
}
list.add(map);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
release();
}
return list;
}
/**批量更新*/
public void excuteBatch(String[] sqls){
conn=this.getConn();
/**设置提交模式为手动提交*/
try{
conn.setAutoCommit(false);
stmt=conn.createStatement();
for(String sql:sqls){
stmt.addBatch(sql);
}
stmt.executeBatch();
/**手动提交*/
conn.commit();//事务
}catch(SQLException e){
e.printStackTrace();
}finally{
release();
}
}
/**释放资源*/
public void release(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}