JDBC
JDBC(Java Database Connectivity)可以为多种关系型数据库提供统一的访问操作接口。
JDBC API:提供各种访问操作接口。
Driver:数据库的驱动程序一般由第三方提供:
Oracle ojdbc-x.jar
MySQL mysql-connector-java-x.jar
SqlServer sqljdbc-x.jar
API:
1.DriverManager 管理驱动
要先加载数据库驱动:
装载MySql驱动:Class.forName(“com.mysql.jdbc.Driver”);
装载Oracle驱动:Class.forName(“oracle.jdbc.driver.OracleDriver”);
2.Connection 数据库连接对象
由 DriverManager 产生,并可以生成数据库操作对象(Statement,PreparedStatement,CallableStatement)
连接MySql数据库:
Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
连接Oracle数据库:
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");
连接SqlServer数据库:
Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://host:port; DatabaseName=database", "user", "password");
方法:
createStatement():创建statement对象。(无参)
prepareStatement(sql) :创建PreparedStatement对象。
prepareCall(sql):创建callableStatement对象。
setAutoCommit(boolean autoCommit):设置事务是否自动提交。
commit() :在链接上提交事务。
rollback() :在此链接上回滚事务。
3.Statement 用于发送SQL语句(增删改查)。
方法:
executeQuery(String sql):运行查询操作,返回ResultSet结果集。
executeUpdate(String sql):运行增/删/改操作,返回更新的行数。
addBatch(String sql) :把多条sql语句放到一个批处理中。
executeBatch():向数据库发送一批sql语句执行。
PreparedStatement 是 Statement 的子接口,有预编译的功能,安全性较高,可以提高性能(推荐使用)。
方法:比Statement多了setXxx()方法(配合占位符‘?’实现预编译)
CallableStatement 是 PreparedStatement 的子接口,用于调用数据库的存储函数/过程。
4.ResultSet 结果集,通常由查询操作返回
方法:
getXxx(字段名|位置) 获取数据库相对应类型的数据对象。
next():移动到下一行,并返回是否存在数据
previous():移动到前一行,并返回是否存在数据
absolute(int row):移动到指定行
ResultSet 的用法和 iterator很相似
JDBC执行流程
1.导入驱动包,加载驱动类 Class.forName("")
2.与数据库建立连接
Connection connection = DriverManager.getConnection(url,name,paw)
3.发送sql,并执行
Statement stmt = connection.createStatement();
int count = stmt.executeUpdate(sql)//返回增删改几条数据
ResultSet rs = s.executeQuery(sql);//返回查询结果集
4.处理结果集
while(rs.next()) {
…
}
5.关闭流和对象(先开后关)
一个简单的JDBC示例
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Test {
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
Statement stmt=con.createStatement();
String sql="select * from dept";
ResultSet rs=stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
}
rs.close();
stmt.close();
con.close();
}
}
Statment 和 PreparedStatement
PreparedStatement 的优点:
1.编码简便(避免拼接)
2.提高性能(编译一次)
3.安全(防sql注入)
一个登录的简单例子:
先创建一个表,初始用户 id 1, name zjj, paw 123.
用Statement模拟登录
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
Statement stmt = null;
Scanner sc = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
stmt=con.createStatement();
sc = new Scanner(System.in);
System.out.println("name:");
String name = sc.nextLine();
System.out.println("paw");
String paw = sc.nextLine();
String sql="select count(*) from login where name ='"+name+"'and paw ='"+paw+"'";
rs=stmt.executeQuery(sql);
int count = -1;
if(rs.next()) {
count = rs.getInt(1);
}
if(count > 0)
System.out.println("登录成功");
else
System.out.println("登录失败");
}catch(SQLException e){
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
sc.close();
stmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
看似没什么问题,其实很严重,因为Statement的sql是采用字符串拼接,如果输入一些敏感字符将造成严重后果。
用PreparedStatment:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
PreparedStatement pstmt = null;
Scanner sc = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
String sql="select count(*) from login where name = ? and paw = ?";//占位符
pstmt=con.prepareStatement(sql);//预处理
sc = new Scanner(System.in);
System.out.println("name:");
String name = sc.nextLine();
System.out.println("paw");
String paw = sc.nextLine();
pstmt.setString(1, name);
pstmt.setString(2, paw);//填充数据
rs=pstmt.executeQuery();
int count = -1;
if(rs.next()) {
count = rs.getInt(1);
}
if(count > 0)
System.out.println("登录成功");
else
System.out.println("登录失败");
}catch(SQLException e){
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
sc.close();
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
String sql="select count(*) from login where name = ? and paw = ?";
pstmt=con.prepareStatement(sql);
PreparedStatment 与 Statement 不同的地方在于pstmt 会进行预处理,sql语句用占位符’?'占位,然后用setXxx()填充数据,再执行。如果是多次的批操作的话,Statment 需要编译多次,而PreparedStatment只需要一次,所以批操作 PreparedStatment 效率要高。
为了防止因为字符串拼接的注入 PreparedStatment 源码中还对敏感字符进行了转义。所以较为安全。
稍大型数据存储
对于大型数据存储有两种方式:
a.存储数据路径
b.CLOB/BLOB
clob: 用于存储大文本数据(小说)。(配合字符流)
blob: 二进制文件(电影,音乐。。。。)(配合字节流)
CLOB存文本:
pstmt.setCharacterStream(2, read, file.length())
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
PreparedStatement pstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
String sql="insert into mynovel values(?,?)";//占位符
pstmt=con.prepareStatement(sql);//预处理
File file = new File("D:\\霸道总裁.txt");
InputStream is = new FileInputStream(file);
Reader read = new InputStreamReader(is , "GBK");
pstmt.setInt(1, 1);
pstmt.setCharacterStream(2, read, file.length());
int count = pstmt.executeUpdate();
if(count > 0)
System.out.println("操作成功");
else
System.out.println("操作失败");
read.close();
is.close();
}catch(SQLException e){
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
CLOB读取(写到文件中)
Reader read = rs.getCharacterStream(“NOVEl”);
import java.io.BufferedWriter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
String sql="select NOVEL from mynovel where id = ? ";//占位符
pstmt=con.prepareStatement(sql);//预处理
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();//结果集
if(rs.next())
{
Reader read = rs.getCharacterStream("NOVEl");
FileOutputStream fos = new FileOutputStream ("src/霸道总裁.txt",true);
OutputStreamWriter osw = new OutputStreamWriter(fos, "GBK");
BufferedWriter writer = new BufferedWriter (osw);
char[] chs = new char[100];
int len = -1;
while( (len = read.read(chs))!=-1)
{
writer.write(chs, 0, len);
}
writer.close();
read.close();
}
}catch(SQLException e){
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
BLOB存MP3文件
pstmt.setBinaryStream(2, is, file.length());
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
PreparedStatement pstmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
String sql="insert into mymusic values(?,?)";//占位符
pstmt=con.prepareStatement(sql);//预处理
File file = new File("D:\\2017年6月四级真题(二).MP3");
InputStream is = new FileInputStream(file);
pstmt.setInt(1, 1);
pstmt.setBinaryStream(2, is, file.length());
int count = pstmt.executeUpdate();
if(count > 0)
System.out.println("操作成功");
else
System.out.println("操作失败");
is.close();
}catch(SQLException e){
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
BLOB读取
InputStream is= rs.getBinaryStream(“MUSIC”);
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl","scott","tiger");
String sql="select MUSIC from mymusic where id = ? ";//占位符
pstmt=con.prepareStatement(sql);//预处理
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();//结果集
if(rs.next())
{
InputStream is= rs.getBinaryStream("MUSIC");
OutputStream os = new FileOutputStream ("src/听力.MP3");
byte[] bs = new byte[100];
int len = -1;
while( (len = is.read(bs))!=-1)
{
os.write(bs, 0, len);
}
os.close();
is.close();
}
}catch(SQLException e){
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}