JDBC以及相关技术学习(五)----PreparedStatement特殊参数处理

PreparedStatement主要有三种特殊的参数，分别为日期类型、大文本流、字节流。

（1）日期类型

先上代码：

 
JDBCToolSingleTon jDBCToolSingleTon = JDBCToolSingleTon.getInstance();
Connection conn = jDBCToolSingleTon.getConnection();

String sql = "insert into UserInfo(name,birthday,account) values(?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS) ;//加上参数后，会自动返回主键
ps.setString(1, user.getName());
ps.setDate(2, new java.sql.Date( user.getBirthday().getTime()));//注意这里两个DATE需要转换
ps.setFloat(3, user.getAccount());
int i =   ps.executeUpdate();
请看红色部分，ps.setDate参数类型为：java.sql.Date，但实际我们使用日期为java.util.date，两者需要互相转换才能使用，转换的方法也很简单，采用：

new java.sql.Date(  java.util.Date对象的getTime() 方法 )

注意：java.sql.Date继承自java.util.Date,但多了两个方法，一个是将字符串转换为DATE，一个是将DATE转换为字符串。

（2）大文本流

注意，大文本流在SQL SERVER里面对应的是text类型

static void create(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JDBCToolSingleTon.getInstance().getConnection();
String sql = "insert into clob_test(big_text) values(?)";
ps = conn.prepareStatement(sql);
File file = new File("文本文件的所在路径");
Reader reader = new BufferedReader(new FileReader(file));
ps.setCharacterStream(1, reader,(int)file.length());
int i = ps.executeUpdate();
reader.close();
JDBCToolSingleTon.getInstance().freeResource(conn, null, ps);}
catch(Throwable e){
e.printStackTrace();
}

}


public static void read() throws SQLException, IOException{
JDBCToolSingleTon jDBCToolSingleTon = JDBCToolSingleTon.getInstance();
Connection conn = jDBCToolSingleTon.getConnection();
Statement statement = conn.createStatement();
String sql = "select big_text from clob_test";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
Clob clob = resultSet.getClob(1);//在JAVA里面string没有限制，也可以直接GetString出来，SetString进去
File file = new File("新文件保存路径");//带缓存的IO操作
Writer writer = new BufferedWriter(new FileWriter(file));
Reader reader =  clob.getCharacterStream();
char[] buff = new char[1024];
for(int i = 0; (i=reader.read(buff))>0;){
writer.write(buff, 0, i);
}
writer.close();
reader.close();
}
 
jDBCToolSingleTon.freeResource(conn, resultSet, statement);

}

（3）字节流 

注意：字节流在SQL SERVER里面对应的是Image类型

static void create(){
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JDBCToolSingleTon.getInstance().getConnection();
String sql = "insert into blob_test(big_bit) values(?)";
ps = conn.prepareStatement(sql);
File file = new File("文件所在路径");
InputStream in = new BufferedInputStream(new FileInputStream(file));
ps.setBinaryStream(1, in, (int)file.length());
int i = ps.executeUpdate();
in.close();
JDBCToolSingleTon.getInstance().freeResource(conn, null, ps);}
catch(Throwable e){
e.printStackTrace();
}

}

public static void read() throws SQLException, IOException{
JDBCToolSingleTon jDBCToolSingleTon = JDBCToolSingleTon.getInstance();
Connection conn = jDBCToolSingleTon.getConnection();
Statement statement = conn.createStatement();
String sql = "select big_bit from blob_test";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()){
 
File file = new File("文件所在路径");
OutputStream outputStream = new BufferedOutputStream(new FileOutputStream(file));
InputStream inputStream =  resultSet.getBinaryStream(1);
byte[] buff = new byte[1024];
for(int i = 0; (i=inputStream.read(buff))>0;){
outputStream.write(buff, 0, i);
}
outputStream.close();
inputStream.close();
}
 
jDBCToolSingleTon.freeResource(conn, resultSet, statement);

}