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();
 }