1. 连接数据库的步骤:
1) 注册驱动
2) 建立连接
3) 创建语句
4) 执行语句
5) 处理结果
6) 释放资源(注意关闭的顺序)
实例//1.注册驱动
- DriverManager.registerDriver(newcom.mysql.jdbc.Driver());
- System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver");
- Class.forName("com.mysql.jdbc.Driver");//推荐方式
- //2.建立连接
- String url = "jdbc:mysql:localhost:3306/jdbc";
- String user = "root";
- String password = "mysql";
- Connection conn = DriverManager.getConnection(url,user,password);
- //3.创建语句
- Statement st = conn.createStatement();
- //4.执行语句
- ResultSet rs = st.executeQuery("select * from user");
- //5.处理结果
- while(rs.next()){//按行来遍历
- System.out.println(rs.getObject(1) + "\t" + rs.getObject(2) + "\t" +rs.getObject(3) + "\t" + rs.getObject(4));
- }
- //6.释放资源(注意关闭的顺序)
- rs.close();
- st.close();
- conn.close();
2. 注意在jdbc中的sql注入问题,解决的办法是使用PreparedStatement :PreparedStatement ps = null;// 利用PreparedStatement而不用Statement是因为PreparedStatement能够对输入的内容进行过滤,除去sql的关键字,防止此问题。
3. 数据库中对大文本的写入和读取:clob类的处理
- public class ClobTest {
- /**
- * @param args
- * @throws Exception
- */
- public static void main(String[] args) throws Exception {
- //create();
- read();
- }
- //数据库的读取操作
- static void read() throws SQLException, Exception {
- Connection conn = null;
- Statement st = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- // 3.创建语句
- st = conn.createStatement();
- // 4.执行语句
- rs = st.executeQuery("select big_text from clob_test");
- // 5.处理结果
- while (rs.next()) {// 按行来遍历
- Reader reader = rs.getCharacterStream(1);
- Filefile = newFile("CRUD_bak.java");
- Writerwriter = 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();
- }
- } finally {
- JdbcUtils.free(rs, st, conn);
- }
- }
- //数据库的创建操作
- static void create() throws SQLException, IOException {
- Connection conn = null;
- PreparedStatement ps = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- // 3.创建语句
- String sql = "insertinto clob_test(big_text) values(?)";
- ps = conn.prepareStatement(sql);
- File file = new File("src/com/lcq/jdbc/CRUD.java");
- Reader reader = newBufferedReader(newFileReader(file));
- //传递大块的文本文档
- ps.setCharacterStream(1,reader, file.length());
- int i = ps.executeUpdate();
- reader.close();
- System.out.println("i = " + i);
- } finally {
- JdbcUtils.free(rs, ps, conn);
- }
- }
- }
4. 问题blob类型读取二进制文件
数据库编译时异常的处理,编写自己的异常类
- package com.lcq.jdbc.impl;
- public class DaoException extends RuntimeException {
- /**
- *
- */
- private static final long serialVersionUID = 1L;
- public DaoException() {
- // TODO Auto-generatedconstructor stub
- }
- public DaoException(String message) {
- super(message);
- // TODO Auto-generatedconstructor stub
- }
- public DaoException(Throwablecause) {
- super(cause);
- // TODO Auto-generatedconstructor stub
- }
- public DaoException(String message, Throwable cause) {
- super(message, cause);
- // TODO Auto-generatedconstructor stub
- }
- }
- 在使用时
- catch(SQLException e){
- //异常的处理,转化为运行时异常
- throw new DaoException(e.getMessage(),e);
- }
在sql中SQLException是编译时异常,所以要抛出,但是如果只是向上抛出的话,会导致业务层和数据访问层的关系复杂,后期维护不便;因此最好的解决方法是在数据访问层中将异常catch住,编写异常类进行处理,在运行时如果上层能够处理就处理。从而使业务层和数据访问层耦合度减少。
5. 一个类的实例化的过程中首先执行的是静态代码块,然后是构造函数。
6.
事务的处理问题:
- /**
- * 事务处理范例
- * 从账户1转账到账户2十元
- *
- */
- static void test() throws SQLException {
- Connection conn = null;
- Statement st = null;
- ResultSet rs = null;
- Savepoint sp = null;
- try {
- conn = JdbcUtils.getConnection();
- // 3.创建语句
- Conn.setAutoCommit(false);//设置自动提交无效
- st = conn.createStatement();
- // 4.执行语句
- String sql = "updateuser set money=money-10 where id=1";
- st.executeUpdate(sql);
- //设置回滚点
- sp =conn.setSavepoint();
- sql = "update userset money=money+10 where id=3";
- st.executeUpdate(sql);
- sql = "selectmoney from user where id=2";
- rs = st.executeQuery(sql);
- float money = 0.0f;
- // 5.处理结果
- if (rs.next()) {// 按行来遍历
- money = rs.getFloat("money");
- }
- if(money > 300){
- throw new RuntimeException("已经达到最大值!");
- }
- sql = "update userset money=money+10 where id=2";
- st.executeUpdate(sql);
- conn.commit();
- } catch(RuntimeException e){
- if(conn != null && sp != null)
- //回滚到设置的点
- conn.rollback(sp);
- //递交,使以上操作有效
- conn.commit();
- throw e;
- }catch(SQLException e){
- if(conn != null)
- conn.rollback();
- throwe;
- }finally {
- JdbcUtils.free(rs, st, conn);
- }
- }
7. 跨越多个数据源的事务(JTA)实现分布式的数据处理,事务处理步骤:
1) 打开事务
2) 提交事务
3) 回滚事务
8. 在数据库中编写组件(函数),通过ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//能够得到数据库中的组件。这样通过组件可以在用户向数据库中添加数据时,在返回时可以用组件对结果进行标识,说明数据已经在数据库中得到更新。
9. 批处理,对sql语句的打包。
10.
根据用户的查询需求进行查询,并将结果封装为Map返回
- public classResultSetMetaDataTest {
- /**
- * @param args
- * @throws SQLException
- */
- public static void main(String[] args) throws SQLException {
- List<Map<String, Object>> datas = read("select * from user");
- System.out.println(datas);
- }
- static List<Map<String, Object>> read(String sql) throws SQLException {
- Connection conn = null;
- PreparedStatement ps = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- ps = conn.prepareStatement(sql);
- rs = ps.executeQuery();
- ResultSetMetaDatarsmd = rs.getMetaData();
- //得到结果集的列数
- intcount = rsmd.getColumnCount();
- String[] colNames = new String[count];
- for(int i = 1; i < colNames.length;i++){
- colNames[i-1] = rsmd.getColumnName(i);
- }
- List<Map<String, Object>> datas = newArrayList<Map<String, Object>>();
- // 5.处理结果
- while (rs.next()) {// 按行来遍历,将每一行添加到map中,list中最后存放的是表
- Map<String,Object> data = new HashMap<String, Object>();
- for(inti = 1;i<= colNames.length;i++){
- data.put(colNames[i-1],rs.getObject(i));
- }
- datas.add(data);
- }
- return datas;
- } finally {
- JdbcUtils.free(rs, ps, conn);
- }
- }
- }
11. 对象与关系的映射(利用反射)
- public class ORMTest {
- /**
- * @param args
- * @throwsInvocationTargetException
- * @throwsIllegalAccessException
- * @throws SQLException
- * @throwsIllegalArgumentException
- * @throws InstantiationException
- */
- public static void main(String[] args) throws IllegalArgumentException,
- SQLException, IllegalAccessException,InvocationTargetException,
- InstantiationException {
- User user = (User) getObject(
- "select idas Id ,name as Name, birthday as Birthday,money as Money from user whereid=2",
- User.class);
- System.out.println(user);
- }
- static ObjectgetObject(String sql, Class clazz) throws SQLException,
- IllegalArgumentException, IllegalAccessException,
- InvocationTargetException, InstantiationException {
- Connection conn = null;
- PreparedStatement ps = null;
- ResultSet rs = null;
- try {
- conn = JdbcUtils.getConnection();
- ps = conn.prepareStatement(sql);
- rs = ps.executeQuery();
- ResultSetMetaData rsmd = rs.getMetaData();
- // 得到结果集的列数
- int count = rsmd.getColumnCount();
- String[] colNames = new String[count];
- for (int i = 1; i <= colNames.length; i++) {
- colNames[i - 1] = rsmd.getColumnLabel(i);
- }
- Object object = null;
- Method[] ms = clazz.getMethods();
- // 5.处理结果
- if (rs.next()) {// 按行来遍历,将每一行添加到map中,list中最后存放的是表
- object = clazz.newInstance();
- for (int i = 0; i < colNames.length; i++) {
- String colName = colNames[i];
- String methodName = "set" + colName;
- // System.out.println(methodName);
- for (Method m : ms) {
- if (methodName.equals(m.getName())) {
- m.invoke(object, rs.getObject(colName));
- }
- }
- }
- }
- return object;
- } finally {
- JdbcUtils.free(rs, ps, conn);
- }
- }
- }
12. 创建数据库连接池,先创建多个连接,将连接一次放在List的尾部,用的时候从List的头部取出连接,使用连接;用完释放连接的时候是将连接返回到连接池的尾部,供后边的用户使用。达到连接重复使用的目地。
- /**
- *
- * 创建数据库连接池
- */
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.SQLException;
- import java.util.LinkedList;
- public class MyDataSource {
- private static String url = "jdbc:mysql://localhost:3306/jdbc";
- private static String user = "root";
- private static String password = "mysql";
- private LinkedList<Connection>connectionsPoll = new LinkedList<Connection>();
- public MyDataSource(){
- for(int i = 0; i<5;i++){
- try {
- this.connectionsPoll.addLast(this.createConnection());
- } catch (SQLException e) {
- throw newExceptionInInitializerError(e);
- }
- }
- }
- public Connection getConnection(){
- return this.connectionsPoll.removeFirst();
- }
- private Connection createConnection() throws SQLException{
- return DriverManager.getConnection(url, user, password);
- }
- public void free(Connection conn){
- this.connectionsPoll.addLast(conn);
- }
- }
13. 对创建连接的优化,利用dbcp作为数据源,从数据源中取出连接。编写自己的数据源。对代码的优化是从“变与不变”入手的。
14. 通过模板模式对增删改查进行优化,利用抽象和继承实现。
15. 利用工厂模式和sping对数据库的封装进行最终版本的实现。