jdbc基础
1.JDBC简介
1.1 JDBC(Java Data Base Connectivity,java数据库连接),由一些接口和类构成的API。
1.2 J2SE的一部分,由java.sql, javax.sql包组成
1.3 应用程序、JDBC API、数据库驱动及数据库之间的关系
应用程序
JDBC
--------------------------------------------------------
| | |
MySQL Driver Oracle Driver DB2 Driver
| | |
MySQL Oracle DB2
2. 连接数据库的步骤
2.1 注册驱动(只做一次)
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
2.2 建立连接(Connection)
Connect conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc",user,password);
2.3 创建语句(Statement)
Statement st = conn.createStatement();
2.4 执行语句(ResultSet)
ResultSet rs = st.excuteQuery("select * from user");
2.5 处理结果
while(rs.next()) {
System.out.println(rs.getObject(1)+rs.getObject(2)+rs.getObject(3)+rs.getObject(4));
}
2.6 释放资源
rs.close(); st.close(); conn.close();
3. 注册驱动
3.1 DriverManager.registerDriver(new com.mysql.jdbc.Driver());
3.2 System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
3.3 Class.forName("com.mysql.jdbc.Driver"); //推荐方式
4. 创建连接
//url格式:JDBC:子协议:子名称//主机名:端口/数据库名?属性名=属性值&...
String url = "jdbc:mysql://localhost:3306/jdbc"
//user,password可用"属性名=属性值"的方式告诉数据库,其它参数如useUnicode=true&characterEncoding=GBK
String user = "root";
password = "";
Connection conn = DriverManager.getConnection(url, user, password);
5. 释放资源
5.1 释放ResultSet, Statement, Connection.
5.2 数据库连接(Connection)是非常稀有的资源,用完后必须马上释放,如果Connection不能及时正确的关闭将导致系统宕机。Connnection的使用原则是尽量晚创建,尽量早的释放。
5 数据库连接模板
pubic class Base { static void template() throws Exception { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection(); st = conn.createStatement(); rs = st.executeQuery("select * from user"); while(rs.next()) { System.out.println(rs.getObject(1)+rs.getObject(2)+rs.getObject(3)+rs.getObject(4)); } }finally{ JdbcUtils.free(rs, st, conn); } } } public final class JdbcUtils { private static String url = "jdbc:mysql://localhost:3306/jdbc"; private static String user = "root"; private static String password = ""; private JdbcUtils() { } static { try { Class.forName("com.mysql.jdbc.Driver"); }catch(ClassNotFoundException e) { throw new ExceptionInInitializerError(e); } } public static Connection getConnection() throws SQLExeption { return DriverManager.getConnection(url,user,password); } public static void free(ResultSet rs,Statement st,Connection conn) { try{ if(rs != null) rs.close(); }catch(SQLException e) { e.printStackTrack(); }finally { try { if(st != null) st.close(); }catch(SQLException e) { e.printStackTrack(); }finally { if(conn != null) try{ conn.close(); }catch(SQLException e) { e.printStackTrack(); } } } } }
二. JDBC
1. CRUD
1.1 Read
String sql = "select id, name, money, birthday from user";
rs = st.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getObject("id") + "\t"
+ rs.getObject("name") + "\t"
+ rs.getObject("birthday") + "\t"
+ rs.getObject("money"));
}
1.2 Create
String sql = "insert into user(name,birthday,money) values ('name1','1987-01-01',400);
int i = st.executeUpdate(sql);
System.out.println("i=" + i);
1.3 Update
String sql = "update user set money=money+10";
int i = st.executeUpdate(sql);
System.out.println("i=" + i);
1.4 Delete
String sql = "delete from user where id>4";
int i = st.executeUpdate(sql);
System.out.println("i=" + i);
2. SQL注入,PreparedStatement和Statement
2.1 在SQL中包含特殊字符或SQL得关键字(如:'or 1 or')时,Statement将出现不可预料的结果(出现异常或查询的结果不正确),可用PreparedStatement来解决。
2.2 PreperedStatement(从Statement扩展而来)相对Statement的优点:
没有SQL注入的问题
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出
数据库和驱动可以对PreperedStatement进行优化(只有在相关联的数据库连接没有关闭的情况下有效)
2.3 示例
String sql = "select id, name, money, birthday from user where name=?";
ps = conn.prepareStatement(sql);
ps = setString(1,name); //把sql中的第一个"?"用"name"替换
rs = ps.executeQuery();
3. jdbc中的数据类型与日期问题
3.1 把java中的日期数据转化为mysql中的日期数据
String sql = "insert into user(name,birthday,money) values(?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1,name);
ps.setDate(2,new java.sql.Date(birthday.getTime()));
ps.setFloat(3,money);
ps.executeUpdate();
3.2 把mysql中的日期数据转化为java中的日期数据
rs = st.executeQuery("select birthday from user where id=" + id);
while(rs.next()) {
birthday = new Date(rs.getDate("birthday").getTime()); //birthday = rs.getDate("birthday").getTime()
}
4. jdbc中大文本数据对象的存取
4.1 大文本数据对象的存储
String sql = "insert into clob_test(big_text) values (?)";
ps = conn.prepareStatement(sql);
File file = new File("src/xxx.java");
Reader reader = new BufferedReader(new FileReader(file));
ps.setCharacterStream(1,reader,(int)file.length());
int i = ps.executeUpdate();
reader.close();
System.out.println("i=" + i);
4.2 大文本数据对象的获取
rs =st.executeQuery("select big_text from clob_test");
while(rs.next()) {
Clob clob = rs.getClob(1);
Reader reader = clob.getCharacterStream();
File file = new File("yyy.java");
Writer writer = new BufferedWriter(new FileWriter(file));
char[] buff = new char[1024];
for(int i=0;(i=reader.read(buff))>0;) {
writer.write(buff,0,i);
}
writer.close();
reader.close();
}
5. jdbc访问二进制类型的数据
5.1 几种特殊且比较常用的类型(详细信息见java.sql.Types)
//1.DATA,TIME,TIMESTAMP----->date, time, datetime
ps.setDate(i,d); ps.setTime(i,t); ps.setTimestamp(i,ts); //存
rs.getDate(i); rs.getTime(i); rs.getTimestamp(i); //取
//2. CLOB---->text
ps.setCharacterStream(index,reader,length); //存
ps.setString(i,s); //存
reader = rs.getCharacterStream(i); //取
reader = rs.getClob(i).getCharacterStream(); //取
string = rs.getString(i); //取
5.2 示例
//存储二进制类型的数据 String sql = "insert into blob_test(big_bit) values(?)"; ps = conn.prepareStatement(sql); File file = new File("IMG_0001.jpg"); InputStream in = new BufferedInputStream(new FileInputStream(file)); ps.setBinaryStream(1,in,(int)file.length()); int i = ps.executeUpdate(); in.close(); System.out.println("i=" + i); //读取二进制类型的数据 rs = st.executeQuery("select big_bit from blob_test"); while(rs.next()) { InputStream in = rs.getBinaryStream(1); File file = new File("IMG_0001.jpg"); OutputStream out = new BufferedOutputStream(new FileOutputStream(file)); byte[] buff = new byte[1024]; for(int i=0;(i=in.read(buff))>0;) { out.write(buf,0,i); } out.close(); in.close(); }