1、连接数据库
package com.society.demo;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* 连接
数据库
*
* @author liyulin lyl010991@126.com
* @version 1.0 2015-01⑴4
*/
public class DBConnection {
private Connection con = null;
private String user = "root";
private String password = "lyl123";
private String serverIp = "localhost";
private String database = "test";
public DBConnection() {
}
public DBConnection(String database, String serverIp) {
this.database = database;
this.serverIp = serverIp;
}
/**
* 加载驱动 建立
数据库连接
*
* @throws ClassNotFoundException
* @throws InstantiationException
* @throws IllegalAccessException
* @throws SQLException
*/
public void connect() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
Properties pr = new Properties();
pr.put("characterEncoding", "UTF⑻");
pr.put("useUnicode", "TRUE");
pr.put("user", this.user);
pr.put("password", this.password);
Class.forName("com.mysql.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://" + this.serverIp + "/" + this.database, pr);
}
/**
* 关闭连接
*/
public void disconnect() {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
/**
* 获得Connection对象
*/
public Connection getCon() {
return con;
}
}
2、操作数据库
package com.society.demo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
* JDBC工具类
*
* @author liyulin lyl010991@126.com
* @version 1.0 2015-01⑴4
*/
public class JDBC {
private DBConnection db = null;
private Connection conn = null;
private PreparedStatement ps = null;
private ResultSet rs = null;
/**
* 建立
数据库连接
*/
public Connection connectDB() {
db = new DBConnection();
try {
db.connect();
conn = db.getCon();
} catch (Exception ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
return conn;
}
/**
* 关闭
数据库
*/
public void closeDB() {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
}
/**
* 履行1条sql语句（增、删、改）
*
* @param sql 插入sql语句
* @param params sql语句中?所对应的值
* @return 是不是插入成功
*/
public boolean executeSQL(String sql, Object[] params) {
boolean tag = false;// 操作是不是成功标志
connectDB();
try {
ps = conn.prepareStatement(sql);
if (null != params) {
for (int i = 0, paramsSize = params.length; i < paramsSize; i++) {
ps.setObject(i + 1, params[i]);
}
}
ps.executeUpdate();
tag = true;
} catch (Exception e) {
e.printStackTrace();
} finally {
closeDB();
return tag;
}
}
/**
* 批量操作（增、删、改）
*
* @param sqls 插入sql语句
* @param objs sql参数（1个2维数组）
* @return
*/
public boolean executeBatch(List<String> sqls, Object[][] objs) {
boolean tag = false;// 批量操作是不是成功标志
connectDB();
try {
conn.setAutoCommit(false);
if (null != objs) {
// sql参数为null
for (int i = 0, size = sqls.size(); i < size; i++) {
String sql = sqls.get(i);
ps = conn.prepareStatement(sql);
if (null != objs[i]) {
for (int j = 0, paramsSize = objs[i].length; j < paramsSize; j++) {
ps.setObject(j + 1, objs[i][j]);
}
}
ps.executeUpdate();
}
} else {
for (int i = 0, size = sqls.size(); i < size; i++) {
String sql = sqls.get(i);
ps = conn.prepareStatement(sql);
ps.executeUpdate();
}
}
conn.commit();
tag = true;
} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
e.printStackTrace();
} finally {
closeDB();
return tag;
}
}
/**
* 履行1条插入语句，同时返回插入时的pk
*
* @param sql
* @param params
* @return pk
*/
public int insertAndGetPk(String sql, Object[] params) {
int key = 0;
connectDB();
try {
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
if (null != params) {
for (int i = 0, paramsSize = params.length; i < paramsSize; i++) {
ps.setObject(i + 1, params[i]);
}
}
ps.executeUpdate();
ResultSet keys = ps.getGeneratedKeys();
if (keys.next()) {
key = keys.getInt(1);
}
conn.commit();
} catch (Exception exception) {
try {
conn.rollback();
exception.printStackTrace();
return 0;
} catch (SQLException ex) {
Logger.getLogger(JDBC.class.getName()).log(Level.SEVERE, null, ex);
}
} finally {
closeDB();
}
return key;
}
/**
* 查询
*
* @param sql sql语句（参数用“?”）
* @param params 参数值
* @return
*/
public ResultSet query(String sql, Object[] params) {
try {
ps = conn.prepareStatement(sql);
if (null != params) {
for (int i = 0, paramsSize = params.length; i < paramsSize; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
} catch (Exception ex) {
ex.printStackTrace();
}
return rs;
}
public static void main(String[] agrs) {
List<String> sqls = new ArrayList<String>();
sqls.add("insert into image(url,title) values(?,?)");
sqls.add("insert into image(url,title) values('2','222')");
sqls.add("insert into image(url,title) values(?,?)");
sqls.add("insert into image(url,title) values(?,?)");
Object[][] objs = new Object[][]{
{"1", "111"}, null, {"3", "333"}, {"4", "444"}
};
JDBC db = new JDBC();
boolean tag = db.executeBatch(sqls, objs);
System.out.println("tag===>" + tag);
}
}