package biogDemo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import demo.JDBCUtils;
public class BasicOperation
{
private static Connection conn = null;
private static PreparedStatement ps = null;
private static ResultSet rs = null;
public static void main(String[] args) throws SQLException
{
// TODO 自动生成的方法存根
conn = JDBCUtils.getConnection();
create();
delete();
update();
read();
JDBCUtils.free(rs, ps, conn);
}
// 创建
public static void create() throws SQLException
{
String sql_1 = "insert into tb_11 values (?,?,?)";
ps = conn.prepareStatement(sql_1);
ps.setInt(1,2);
ps.setString(2,"sjjhong");
ps.setFloat(3,100f);
ps.execute();
/* 如果第一个结果是 ResultSet 对象,则返回 true;如果第一个结果是更新计数或者没有结果,则返回 false */
}
// 删除
public static void delete() throws SQLException
{
String sql_2 = "delete from tb_11 where id = ?";
ps = conn.prepareStatement(sql_2);
ps.setInt(1,2);
ps.execute();
}
//更新
public static int update() throws SQLException
{
String sql_3 = "update tb_11 set money = money + ?";
ps = conn.prepareStatement(sql_3);
ps.setFloat(1,100f);
int result = ps.executeUpdate();
return result;
}
// 查找
public static void read() throws SQLException
{
String sql_4 = "select id,name,money from tb_11 where id >= ?";
ps = conn.prepareStatement(sql_4);
ps.setInt(1,1);
rs = ps.executeQuery();
int count = rs.getMetaData().getColumnCount();
while(rs.next())
{
for(int i = 1;i <= count ;i++)
{
System.out.print(rs.getObject(i) + " ");
}
System.out.println("\n");
}
}
}
create table tb_13(
id INT,
jpg BLOB
);
package demo;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BlobDemo
{
public static void main(String[] args)
{
try
{
// insert();
read();
}
catch (Exception e)
{
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
public static void insert() throws FileNotFoundException
{
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
String sql = "insert into tb_13(id,jpg) values (?,?)";
File file = new File("src\\demo\\binary_data.jpg");
try
{
conn = JDBCUtils.getConnection();//建立连接
ps = conn.prepareStatement(sql);//创建语句
ps.setInt(1,1);
ps.setBinaryStream(2,new FileInputStream(file),file.length());//替换占位符为流对象中的内容
ps.executeUpdate();//执行语句
System.out.println("insert success...");
}
catch (SQLException e)
{
// TODO: handle exception
System.out.println("insert failed...");
e.printStackTrace();
}
finally
{
JDBCUtils.free(rs, ps, conn);
}
}
public static void read()
{
Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;
String sql = "select id,jpg from tb_13 where id = ?";
try
{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1,1);
BufferedOutputStream bufo =
new BufferedOutputStream(new FileOutputStream("src\\demo\\blobData.jpg"));
rs = ps.executeQuery();
if(rs.next())//处理结果集---->这里已经明确结果集的大小为0或者1,故用if
{
Blob blob = rs.getBlob("jpg");//通过getBlob方法获取Blob对象
BufferedInputStream bufi =
new BufferedInputStream(blob.getBinaryStream());//封装inputStream对象
byte[] buffer = new byte[1024];
int len = 0;
while((len = bufi.read(buffer)) != -1)
{
bufo.write(buffer,0, len);//写入文件
bufo.flush();
}
bufo.close();
bufi.close();
System.out.println("read success....");
}
}
catch (SQLException | IOException e)
{
// TODO: handle exception
System.out.println("read failed...");
e.printStackTrace();
}
finally
{
JDBCUtils.free(rs, ps, conn);
}
}
}
create table tb_14(
id INT,
content TEXT
);
package demo;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
public class ClobDemo
{
/**
* @param args
*/
public static void main(String[] args)
{
// TODO 自动生成的方法存根
// insert();
read();
}
public static void insert()
{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "insert into tb_14(id,content) values (?,?)";
File file = new File("src\\demo\\clobDemo.java");
try
{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1,1);
ps.setCharacterStream(2,new FileReader(file), file.length());
ps.execute();
System.out.println("insert success...");
}
catch (SQLException | FileNotFoundException e)
{
System.out.println("insert failed...");
// TODO: handle exception
e.printStackTrace();
}
finally
{
JDBCUtils.free(rs,ps, conn);
}
}
public static void read()
{
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "select id,content from tb_14 where id = ?";
String columnLabel = "content";
try
{
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 1);
rs = ps.executeQuery();//执行查询语句
ResultSetMetaData rsmd = rs.getMetaData();//获取结果集元数据对象
// BufferedWriter bufw =
// new BufferedWriter(new FileWriter("src\\demo\\ClobDemo_cpy.java"));
PrintWriter pw = new PrintWriter(new File("src\\demo\\ClobDemo_cpy.txt"));//用打印流换行较方便
String line = null;
if(rs.next())
{
for(int i = 0; i< rsmd.getColumnCount(); i++)//遍历表中的各列
{
if(rsmd.getColumnName(i+1).equals(columnLabel))//如果该列的名字等于columnLabel的值,则进入if语句
{
Clob clob = rs.getClob(columnLabel);//通过getClob方法获取clob对象
Reader re = clob.getCharacterStream();//通过Clob对象的getCharacterStream方法获取流
BufferedReader bufr = new BufferedReader(re);//封装流
while((line = bufr.readLine()) != null)
{
// bufw.write(line);//写入文件
// bufw.flush();
pw.println(line);
pw.flush();//这里需要刷新
}
// bufw.close();
bufr.close();
System.out.println("read success...");
}
}
}
}
catch (SQLException | IOException e)
{
// TODO: handle exception
System.out.println("read failed...");
e.printStackTrace();
}
finally
{
JDBCUtils.free(rs,ps, conn);
}
}
}
PreparedStatementps = null;
ps.setInt(1, m.getId());
ps.setString(2,m.getName());
ps.setString(3,m.getSex());
ps.setInt(4,m.getAge());
这样我们就完成了对于前四位占位符的赋值操作,那么,我们如何为具有特殊类型的占位符birthday赋值呢?
由于在MySQL数据库中,birthday字段的类型是Date类型的,所以,我们在保证数据类型的一致性问题上就得进行考虑,否则就会导致程序出错,数据不能添加到数据库中。
这里,介绍两种保险的赋值方式:
情况1、
将Person类中birthday的类型定义为String类型,这样我们只需在设置birthday字段的占位符的值时,只需将其设置为ps.setString(5,m.getBirthday());即可。
情况2、
在为Person中birthday属性设置类型时将其强制设置为java.sql.Datebirtthday即可。
这样我们在为birthday字段的占位符的值时,只需将其设置为ps.setString(5, java.sql.Date.valueOf("1991-10-13"));即可因为在java.sql.Date中的valueOf()方法可以将 JDBC 日期转义形式的表示 "yyyy-mm-dd" 形式的日期的字符串转换成Date
值。
这样,我们就不会出现由于数据类型不一致导致的数据无法插入到数据库中的问题了。
以上就是本篇的内容了,下一篇我们介绍JDBC中的元数据,事务处理,批量插入,隔离级别等内容。