CONNECTION

package com.briup.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.briup.common.ConnectionFactory;

public class PreparedStatementTest {

private Connection conn;
private PreparedStatement ps;
private ResultSet rs;
private Statement stmt;

public void insertListForPs(List<Person> list){
try {
conn = ConnectionFactory.getConnection();
String sql = "insert into person(id,name,age) values(?,?,?)";
ps = conn.prepareStatement(sql);
//设置自动提交为false
conn.setAutoCommit(false);
int i = 0;
for(Person p:list){
i++;
ps.setLong(1, p.getId());
ps.setString(2, p.getName());
ps.setInt(3, p.getAge());
//把数据添加到批处理中
ps.addBatch();
if(i==100){//30条一次批处理
//统一处理这个批处理 
ps.executeBatch();
i=0;
}
}
ps.executeBatch();
conn.commit();

} catch (Exception e) {
e.printStackTrace();
}finally{
try {
ConnectionFactory.close(ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void stmtForDate(){
try {
conn = ConnectionFactory.getConnection();
stmt = conn.createStatement();
java.util.Date date = new Date();


// String sql = "insert into student(myday) values('23-8月-13')";
String sql = "insert into student(myday) values(to_date('12-02-13','dd-mm-yy'))";
System.out.println(sql);
stmt.execute(sql);
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
ConnectionFactory.close(stmt, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void psForDate(){
try {
conn = ConnectionFactory.getConnection();
String sql = "insert into student(myday) values(?)";

ps = conn.prepareStatement(sql);
java.util.Date d1 = new java.util.Date();
long time = d1.getTime();
java.sql.Date d2 = new java.sql.Date(time);
java.sql.Timestamp timestamp = new Timestamp(time);
ps.setDate(1, d2);
// ps.setTimestamp(1, timestamp);
ps.execute();
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
ConnectionFactory.close(ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public void insertListForStms(List<Person> list){

try {
conn = ConnectionFactory.getConnection();
stmt = conn.createStatement();
String sql = "";

for(Person p:list){
long id = p.getId();
String name = p.getName();
int age = p.getAge();
sql = "insert into person(id,name,age) values("+id+",'"+name+"',"+age+")";
stmt.execute(sql);
}


} catch (Exception e) {
e.printStackTrace();
}finally{
try {
ConnectionFactory.close(stmt, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}

public void insert(Person p){
try {
conn = ConnectionFactory.getConnection();
String sql = "insert into person(id,name,age) values(?,?,?)";
ps = conn.prepareStatement(sql);

//用值把sql语句中的?替换掉
ps.setLong(1, p.getId());
ps.setString(2, p.getName());
ps.setInt(3, p.getAge());
ps.execute();//这时候一定不要把sql语句放进去 错的：ps.execute(sql);

} catch (Exception e) {
e.printStackTrace();
}finally{
try {
ConnectionFactory.close(ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
PreparedStatementTest pst = new PreparedStatementTest();

// List<Person> list = new ArrayList<Person>();
// Person p = null;
// for(int i=1;i<2001;i++){
// p=new Person(i,"tom",20);
// list.add(p);
// }
// long start = System.currentTimeMillis();
//// pst.insertListForStms(list);438
// pst.insertListForPs(list);
// long end = System.currentTimeMillis();
// System.out.println(end-start);
// pst.psForDate();
pst.stmtForDate();

}
}




package com.briup.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionFactory {
private static String driver ;
private static String url ;
private static String username ;
private static String password ;

static{
try {
Properties p = new Properties();
// p.load(new FileInputStream(new File("src/com/briup/common/oracle.properties")));
// p.load(ConnectionFactory.class.getResourceAsStream("oracle.properties"));
p.load(ConnectionFactory.class.getClassLoader().getResourceAsStream("com/briup/common/oracle.properties"));
driver = p.getProperty("driver");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
// System.out.println(url);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws Exception{
Class.forName(driver);
Connection conn = DriverManager.getConnection(url,username,password);
return conn;
}
public static void close(ResultSet rs,Statement stmt,Connection conn) throws Exception{
if(rs!=null)rs.close();
if(stmt!=null)stmt.close();
if(conn!=null)conn.close();
}
public static void close(Statement stmt,Connection conn) throws Exception{
close(null,stmt,conn);
}
// public static void main(String[] args) {
//
// }
}