JDBC(Java DataBase Connectivity)
可以为多种关系型数据库DBMS提供统一的访问方式,用Java来操作数据。
1、JDBC API:提供各种操作访问接口。
主要功能:三件事,具体通过一下类/接口实现
- | - |
---|---|
DriverManager | 管理jdbc驱动 |
Connection | 连接 |
Statement(PreparedStatement) | 增删改查 |
CallableStatement | 调用数据库中的存储过程/存储函数 |
Result | 返回的结果集 |
2、jdbc访问数据库的具体步骤:
a.导入驱动,加载具体的驱动类
b.与数据库建立连接
c.发送sql,执行
d.处理结果集(查询)
3、数据库驱动
- | 驱动jar | 具体驱动类 | 连接字符串 |
---|---|---|---|
Oracle | ojdbc-x.jar | oracle.jdbc.OracleDriver | jdbc:oracle:thin:@localhost:1521:ORCL |
MySQL | mysql-connector-java-x.jar | com.mysql.jdbc.Driver | jdbc:mysql://localhost:3306/数据库实例名 |
SqlServer | sqljdbc-x.jar | com.microsoft.sqlserver.jdbc.SQLServerDriver | jdbc:microsoft:sqlserver:localhost:1433;databasename=数据库实例名 |
例:
public void update()//增删改
{
private final String URL = "jdbc:mysql://localhost:3306/login?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true";
private final String USERNAME = "root";
private final String PWD = "123456";
a.导入驱动,加载具体的驱动类
Class.forName("com.mysql.cj.jdbc.Driver");
b.与数据库建立连接
Connection connection = DriverManager.getConnection(URL,USERNAME,PWD);
c1.发送sql,执行(增删改、查)
Statement stmt = connection.createStatement();
String sql = "insert into --- values(---)";增
String sql = "update --- set --- where ---";改
String sql = "delete from --- where ---";删
//String sql = "select --- from ---";查
执行SQL(增删改:executeUpdate(),查executeQuery())
int count = stmt.executeUpdate(sql);返回值表示增删改几条数据
//ResultSet rs = stmt.executeQuery(sql);
c2.
String sql = "insert into --- values(?,?,?)";
PreparedStatement pstmt = connection.prepareStatement(sql);//预编译
pstmt.setIn(1,--);
pstmt.setString(2,--);
pstmt.setInt(3,--);
d.处理结果
if(count > 0)
System.out.println("操作成功!");
//while(rs.next())
//{
// int sno = rs.getInt("---");
// String sname = rs.getString("---");
//}
if(rs!=null) rs.close();
if(stmt!=null) stmt.close();
if(connection!=null) connection.close();
}
PreparedStatement与Statement
推荐使用PreparedStatement:
1、编码更加简便(避免了字符串的拼接)
例:
String name = "zs";
int age = 23;
stmt:
String sql = "insert into student(stuno,stuname) values('"+name+"',"+age+")";
stmt.executeUpdate(sql);
pstmt:
String sql = "insert into student(stuno,stuname) values(?,?)";
pstmt = connection.prepareStatement(sql);//预编译
pstmt.setString(1,name);
pstmt.setInt(2,age);
pstmt.executeUpdate();
2、安全(可以有效防止sql注入)
sql注入:将客户输入的内容和开发人员的SQL语句混为一体。
callableStatement:
调用存储过程、存储函数
connection.prepareCall(参数:存储过程或存储函数名)
参数格式:
存储过程(无返回值,用Out参数代替):
{ call 存储过程名(参数列表)}
存储函数(有返回值)
{ ? = call 存储函数名(参数列表)}
例:
存储过程:
create or replace procedure addTwoNum(num1 in number,num2 in number,result out number )
as
begin
result:=num1+num2;
end;
/
CallableStatement cstmt = connection.prepareCall("{call addTwoNum(?,?,?)}");
cstmt.setInt(1,10);
cstmt.setInt(2,10);
//设置输出参数
cstmt.registerOutParameter(3,Types.INTEGER);
cstmt.execute();//num1+num2
int result = cstmt.getInt(3);//获取计算结果
注:
如果通过sqlplus访问数据库,只需要开启:OracleServiceSID
通过其他程序访问数据(sqldevelop、navicate、JDBC),需要开启:OracleServiceSID和XxxListener
存储函数:
create or replace function addTwoNumfunction(num1 in number,num2 in number ) -- 1+2 ->3
return number
as
result number;
begin
result:=num1+num2;
return result;
end;
/
CallableStatement cstmt = connection.prepareCall("{? = call addTwoNumfunction(?,?)}");
cstmt.setInt(2,10);
cstmt.setInt(3,10);
cstmt.registerOutParameter(1,Types.INTEGER);
cstmt.execute();//num1+num2
int result = cstmt.getInt(1);//获取计算结果
处理CLOB[Text]/BLOB类型
处理稍大型数据:
a :存储路径
例:JDBC将路径以字符串形式存储到数据库中
b:
oracle
CLOB:大文本数据(小说)
BLOB:二进制文件(电影,音频)
CLOB
例:
create table mynovel (id number primary key,novel clob);
存
String sql = "insert into mynovel values(?,?)";
prepareStatement pstmt = connection.prepareStatement(sql);
pstmt.setInt(1,1);
File file = new File("路径");
InputStream in = new FileInputStream(file);
Reader reader = new InputStreamReader(in,"UTF-8");//转换流可以设置编码
pstmt.setCharacterStream(2,reader,(int)file.length());
int count = pstmt.executeUpdate();
查
String sql = "select NOVEL from nynovel where id = ?";
pstmt = connection.prepareStatment(sql);
pstmt.setInt(1,1);
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
Reader reader = rs.getCharacterStream("NOVEL");
Writer writer = new FileWriter("路径");
char[] chs = new char[100];
int len = -1;
while((len = reader.read(chs))!=-1)
{
writer.write(chs,0,len);
}
writer.close();
reader.close();
}