JDBC的详细讲解和注意事项
-
JDBC简单一句话,就是用java代码去控制数据库,对数据库进行增删改查
-
JDBC 的相关API 总结
-
最常用是阿里巴巴的德鲁伊数据库连接池技术
-
数据库连接步骤
- 必须先创建数据库哈
- 引入德鲁伊的jar包
- 加入配置文件(properties),要放在src目录下,根据一些提示信息去做相应的配置,如果是web程序,需要用properties
-
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); } } }
-
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); } } }
- UserDao 然后再去继承 BasicDao 范型是一个javabean
package com.yc.ajax.dao; import com.yc.ajax.entity.User; public class UserDao extends BasicDAO<User>{ }
- 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; } }
- 最后拿到返回的结果进行判断或者修改数据库
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", "king@qq.com", "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); } }