JDBC的详细讲解和注意事项

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

package com.yc.ajax.dao;

import com.yc.ajax.entity.User;

public class UserDao extends BasicDAO<User>{
}

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