JdbcUtil
package jdbcConnection;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
* jdbc工具类
*
* @author happyqing
*
*/
public class JdbcUtil {
private static Properties env = new Properties();
static {
try {
//JdbcUtil.class.getResourceAsStream("/env.properties"); // /com/cici/conf/env.properties
InputStream is = JdbcUtil.class.getClassLoader().getResourceAsStream("env.properties");
env.load(is);
is.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
public static Connection getConnection() {
Connection conn = tl.get();
try {
if (conn == null) {
Class.forName(env.getProperty("jdbc.driverClassName"));
conn = DriverManager.getConnection(env.getProperty("jdbc.url"),
env.getProperty("jdbc.username"),
env.getProperty("jdbc.password"));
tl.set(conn);
}
// 设置不自动提交
conn.setAutoCommit(false);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 创建PreparedStatement
*
* @param conn
* @param sql
* @return
*/
public static PreparedStatement getPreparedStatement(Connection conn,
String sql) {
PreparedStatement psmt = null;
if (conn != null) {
try {
psmt = conn.prepareStatement(sql);
} catch (Exception e) {
e.printStackTrace();
}
}
return psmt;
}
/**
* 释放资源
*/
public static void close(ResultSet rs, Statement stm, Connection conn) {
if (rs != null)
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
if (stm != null)
try {
stm.close();
} catch (Exception e) {
e.printStackTrace();
}
if (conn != null)
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// 测试
public static void main(String[] args) {
Connection conn = JdbcUtil.getConnection();
System.out.println(conn);
}
}
env.properties
jdbc.driverClassName= oracle.jdbc.driver.OracleDriver
jdbc.url= jdbc:oracle:thin:@192.168.1.186:1521:orcl
jdbc.username=xxx
jdbc.password=xxx
crud
package jdbcConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import utils.JdbcUtil;
public class CrudTest {
private static void add() throws SQLException {
// TODO Auto-generated method stub
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
String sql = "insert into [user](name,password,email,age,birthday,money) values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "psName");
ps.setString(2, "psPassword");
ps.setString(3, "jkjs@126.com");
ps.setInt(4, 23);
ps.setDate(5, new java.sql.Date(new java.util.Date().getDate()));
ps.setFloat(6, 2344);
ps.executeUpdate();
} finally {
JdbcUtil.close(rs, ps, conn);
}
}
private static void get() throws SQLException {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps =null;
ResultSet rs = null;
try {
conn= JdbcUtil.getConnection();
String sql = "select id,name,password,email,birthday,money from [user]";
ps = conn.prepareStatement(sql);
rs= ps.executeQuery();
while(rs.next()){
int id= rs.getInt("id");
String name =rs.getString("name");
String pass=rs.getString("password");
String email = rs.getString("email");
Date birthday =rs.getDate("birthday");
float money= rs.getFloat("money");
System.out.println("id是:"+id+"姓名是: "+name+" 密码是:"+pass+"邮箱是:"
+email+"生日是: "+birthday+"工资是"+money);
}
} finally{
JdbcUtil.close(rs, ps, conn);
}
}
private static void update() throws SQLException {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps =null;
ResultSet rs = null;
try {
conn= JdbcUtil.getConnection();
String sql = "update [user] set name='lucy',password='123',money=5000 where id=1";
ps= conn.prepareStatement(sql);
ps.executeUpdate();
} finally{
JdbcUtil.close(rs, ps, conn);
}
}
private static void delete() throws SQLException {
// TODO Auto-generated method stub
Connection conn=null;
PreparedStatement ps =null;
ResultSet rs = null;
try {
conn= JdbcUtil.getConnection();
String sql = "delete from [user] where id=1";
ps= conn.prepareStatement(sql);
ps.executeUpdate();
} finally{
JdbcUtil.close(rs, ps, conn);
}
}
}
Jdbc事务管理
模拟银行取帐
TransferTest.java
package test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import util.JdbcUtil;
public class TransactionTest {
public static void main(String[] args)throws SQLException {
transferTest();
}
public static void transferTest()throws SQLException{
int id1 =3;
float m1= 100.0f;
int id2 = 4;
transfer2(id1,m1,id2);
}
public static boolean transfer2(int id1,float m1,int id2)throws SQLException{
boolean flag = false;
Connection conn =null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JdbcUtil.getConnection();
conn.setAutoCommit(false);
String sql = "update [user] set money = money=? where id = ?";
ps= conn.prepareStatement(sql);
ps.setFloat(1, m1);
ps.setInt(2, id1);
ps.executeUpdate();
String sql1 = "update [user] set money = money+? where id = ?";
ps= conn.prepareStatement(sql);
ps.setFloat(1, m1);
ps.setInt(2, id2);
ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
// TODO 自动生成 catch 块
conn.rollback();
throw e;
}finally{
JdbcUtil.close(rs, ps, conn);
}
return flag;
}
}
java读取properties文件
http://happyqing.iteye.com/admin/blogs/1966014
JDBC连接各种数据库方法
http://happyqing.iteye.com/admin/blogs/1966028
使用JDBC进行批处理
http://happyqing.iteye.com/admin/blogs/1965951