这段时间在学习JAVA WEB,记录一些数据库连接语句,以防文件丢失等等,以供参考
第一种方式连接:JDBC,使用JDBC前提要创建lib文件夹,把jar文件放入其中,再build path就可以用了
try {
//1 注册驱动,为固定代码
Class.forName("com.mysql.jdbc.Driver");
//2 获得链接,此处可设置服务器端口和选择数据库,以及数据库账号和密码
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/group2","root","root");
//3 获得语句执行者
Statement st = conn.createStatement();
//4 执行语句
ResultSet rs = st.executeQuery("select * from classinfo");
//5 输出结果
while(rs.next()) {
int user_id = rs.getInt("ID"); //通过列名获得值
String user_name = rs.getString("COLLEGE");
System.out.print(user_id+ " : " + user_name+"\n");
}
//6 释放资源
rs.close();
}catch(Exception ex) {
}
第二种方式是使用DataSource连接数据库,注:此处会创建多个文件,其中会注释每个步骤的含义
1.此处用的是配置文件连接方式。在src路径下创建c3p0-config.xml,名字必须为这个
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="user">root</property>
<property name="password">root</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<!-- 设置端口号和选择数据库 -->
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/web13</property>
<!-- 初始化池大小 -->
<property name="initialPoolSize">2</property>
<!-- 最大空闲时间 -->
<property name="maxIdleTime">30</property>
<!-- 最多有多少个连接 -->
<property name="maxPoolSize">10</property>
<!-- 最少几个连接 -->
<property name="minPoolSize">2</property>
<!-- 每次最多可以执行多少个批处理语句 -->
<property name="maxStatements">50</property>
</default-config>
</c3p0-config>
2.创建DataSourceUtils.java文件,类名以及包名看下面,
package com.itheima.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DataSourceUtils {
private static DataSource dataSource = new ComboPooledDataSource();
private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
//threadlocal而是一个线程内部的存储类,可以在指定线程内存储数据,数据存储以后,只有指定线程可以得到存储数据
// 直接可以获取一个连接池
public static DataSource getDataSource() {
return dataSource;
}
// 获取连接对象
public static Connection getConnection() throws SQLException {
Connection con = tl.get();
if (con == null) {
con = dataSource.getConnection();
tl.set(con);
}
return con;
}
// 开启事务
public static void startTransaction() throws SQLException {
Connection con = getConnection();
if (con != null) {
con.setAutoCommit(false);
}
}
// 事务回滚
public static void rollback() throws SQLException {
Connection con = getConnection();
if (con != null) {
con.rollback();
}
}
// 提交并且 关闭资源及从ThreadLocall中释放
public static void commitAndRelease() throws SQLException {
Connection con = getConnection();
if (con != null) {
con.commit(); // 事务提交
con.close();// 关闭资源
tl.remove();// 从线程绑定中移除
}
}
// 关闭资源方法
public static void closeConnection() throws SQLException {
Connection con = getConnection();
if (con != null) {
con.close();
}
}
public static void closeStatement(Statement st) throws SQLException {
if (st != null) {
st.close();
}
}
public static void closeResultSet(ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
}
}
3.创建LoginServlet.java文件,此处我测试的功能是验证用户名和密码是否正确
package com.itheima.login;
import java.io.IOException;
import java.sql.SQLException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import com.itheima.domain.User;
import com.itheima.utils.DataSourceUtils;
public class LoginServlet extends HttpServlet {
@Override
public void init() throws ServletException {
//在Seveltcontext域中存一个数据count
int count = 0;
this.getServletContext().setAttribute("count", count);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//1、获得用户名和密码
String username = request.getParameter("username");
String password = request.getParameter("password");
//2、从数据库中验证该用户名和密码是否正确
QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
String sql = "select * from user where username=? and password=?";
User user = null;
try {
user = runner.query(sql, new BeanHandler<User>(User.class), username,password);
} catch (SQLException e) {
e.printStackTrace();
}
//3、根据返回的结果给用户不同显示信息
if(user!=null){
//从servletcontext中取出count进行++运算
ServletContext context = this.getServletContext();
Integer count = (Integer) context.getAttribute("count");
count++;
//用户登录成功
response.getWriter().write(user.toString()+"---you are success login person :"+count);
context.setAttribute("count", count);
}else{
//用户登录失败
response.getWriter().write("sorry your username or password is wrong");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
PS:后台处理逻辑基本就这样,这里来捋一捋ComboPooledDataSource用配置文件连接数据库的思路
1.在src下创建c3p0-config.xml文件,填写数据库的账户和密码
2.
ComboPooledDataSource pool = new ComboPooledDataSource();
//空参,自动到classpath目录下面加载“c3p0-config.xml”配置文件---配置文件的存储位置和名称必须是这样,且使用“默认配置”
上面用了封装,逻辑会比较绕一点,后续再有深入学习或是新的理解会补充更新