• JDBC簡單一句話，就是用java代碼去控制資料庫，對資料庫進行增刪改查

• JDBC 的相關API 總結

• 最常用是阿里巴巴的德魯伊資料庫連接池技術

• 資料庫連接步驟

1. 必須先創建資料庫哈
2. 引入德魯伊的jar包
3. 加入組態檔(properties),要放在src目錄下，根據一些提示資訊去做相應的配置，如果是web程式，需要用properties

4. JDBCUtilsByDruid工具類連接資料庫

   package com.yc.ajax.Utils;
   
   import com.alibaba.druid.pool.DruidDataSourceFactory;
   
   import javax.sql.DataSource;
   import java.io.FileInputStream;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.util.Properties;
   
   /**
    * 連接資料庫
    */
   public class JDBCUtilsByDruid {
   
       private static DataSource ds;
   
   
       //在靜態代碼塊完成 ds初始化
       static {
           Properties properties = new Properties();
           try {
               //目前我們是javaweb方式啟動，所以要獲得src目錄下的檔案，需要用類加載器
               properties.load(JDBCUtilsByDruid.class.getClassLoader().getResourceAsStream("druid.properties"));
               ds = DruidDataSourceFactory.createDataSource(properties);
           } catch (Exception e) {
               e.printStackTrace();
           }
   
       }
   
       //撰寫getConnection方法
       public static Connection getConnection() throws SQLException {
           return ds.getConnection();
       }
   
       //關閉連接, 老師再次強調： 在資料庫連接池技術中，close 不是真的斷掉連接
       //而是把使用的Connection物件放回連接池
       public static void close(ResultSet resultSet, Statement statement, Connection connection) {
   
           try {
               if (resultSet != null) {
                   resultSet.close();
               }
               if (statement != null) {
                   statement.close();
               }
               if (connection != null) {
                   connection.close();
               }
           } catch (SQLException e) {
               throw new RuntimeException(e);
           }
       }
   }

    

5. BasicDao 根據連接的資料庫 用德魯伊去處理改查

   package com.yc.ajax.dao;
   
   import com.yc.ajax.Utils.JDBCUtilsByDruid;
   import org.apache.commons.dbutils.QueryRunner;
   import org.apache.commons.dbutils.handlers.BeanHandler;
   import org.apache.commons.dbutils.handlers.BeanListHandler;
   import org.apache.commons.dbutils.handlers.ScalarHandler;
   
   import java.sql.Connection;
   import java.sql.SQLException;
   import java.util.List;
   
   /**
    *  根據連接的資料庫 用德魯伊去處理改查
    * @param <T>
    */
   public class BasicDAO<T> {//泛型指定具體型別 需要創建一個javabean
       private QueryRunner qr = new QueryRunner();
   
       //開發通用的 dml 針對任意的表
       //改
       public int update(String sql, Object... parameters) {
           Connection connection = null;
           try {
               connection = JDBCUtilsByDruid.getConnection();
   
               int update = qr.update(connection, sql, parameters);
               return update;
           } catch (SQLException e) {
               throw new RuntimeException();
           } finally {
               JDBCUtilsByDruid.close(null, null, connection);
           }
       }
   
       //回傳多個物件(即查詢的結果是多行), 針對任意表
       /**
        * @param sql        sql 陳述句，可以有 ?
        * @param clazz      傳入一個類的 Class 物件 比如 Actor.class
        * @param parameters 傳入 ? 的具體的值，可以是多個
        * @return 根據 Actor.class 回傳對應的 ArrayList 集合
        */
   
       public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
           Connection connection = null;
   
           try {
               connection = JDBCUtilsByDruid.getConnection();
               List<T> query = qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
               return query;
           } catch (SQLException e) {
               throw new RuntimeException(e);
           } finally {
               JDBCUtilsByDruid.close(null, null, connection);
           }
       }
   
   
       //查詢單行結果 的通用方法
       public T querySingle(String sql, Class<T> clazz, Object... parameters) {
           Connection connection = null;
           try {
               connection = JDBCUtilsByDruid.getConnection();
               return qr.query(connection,sql, new BeanHandler<T>(clazz), parameters);
           } catch (SQLException e) {
               throw new RuntimeException(e);
           } finally {
               JDBCUtilsByDruid.close(null, null, connection);
           }
       }
   
       //查詢單行單列的方法,即回傳單值的方法
       public Object queryScalar(String sql, Object... parameters) {
           Connection connection = null;
   
           try {
               connection = JDBCUtilsByDruid.getConnection();
               return qr.query(connection,sql,new ScalarHandler(),parameters);
           } catch (SQLException e) {
               throw new RuntimeException(e);
           }finally {
               JDBCUtilsByDruid.close(null,null,connection);
           }
       }
   }

    

6.  UserDao 然后再去繼承 BasicDao 范型是一個javabean

   package com.yc.ajax.dao;
   
   import com.yc.ajax.entity.User;
   
   public class UserDao extends BasicDAO<User>{
   }

    

7. UserServict 去創建 UserDao的實體然后咱根據業務邏輯呼叫里面改或者查的方法

   import com.yc.ajax.dao.UserDao;
   import com.yc.ajax.entity.User;
   
   public class UserServlet {
   
       private UserDao userDao = new UserDao();
   
       public User getUserByName(String username){
   
           User user = userDao.querySingle("select * from user1 where name=?", User.class, username);
           return user;
       }
   }

8. 最后拿到回傳的結果進行判斷或者修改資料庫

   public class CheckUserServlet extends HttpServlet {
   
       private UserServlet userServlet = new UserServlet();
       @Override
       protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           System.out.println("CheckUserServlet .. 被呼叫");
   
           response.setContentType("text/html;charset=utf-8");
           //接收 ajax 提交的資料
           String username = request.getParameter("username");
   //        if ("king".equals(username)){
   //            User king = new User(100, "king", "[email protected]", "12345");
   //            String user_king = new Gson().toJson(king);
   //            response.getWriter().write(user_king);
   //        }else {
   //            response.getWriter().write("");
   //        }
           User userByName = userServlet.getUserByName(username);
           if (userByName != null){ //說明用戶已經存在
               //回傳一個json
               Gson gson = new Gson();
               String s = gson.toJson(userByName);
               response.getWriter().write(s);
           }else {
               response.getWriter().write("");
           }
   
   
       }
   
       @Override
       protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           doGet(request,response);
       }
   }

    

標籤：Java

上一篇：Java Long類rotateLeft()方法功能簡介說明

下一篇：JavaSe階段基礎語法