一、直接连接,不封装到工具类中,主要步骤:
先导包:mysql-connector-java-5.0.8-bin.jar(点击跳转到下载界面),放在WebRoot/WEB-INF/lib/下
1.加载驱动//com.MySQL.jdbc.Driver
2.获取连接 Connection对象
3.获取用于向数据库发送SQL的Statement对象
4.执行sql,获取数据,解析数据
5.关闭连接,释放资源
- /* 协议:子协议://主机:端口/数据库名 */
- String url = "jdbc:mysql://localhost:3306/jdbctest";
- // mysql数据库的用户名与密码,安装时自己设置,一般默认为root
- String user = "root";
- String password = "root";
- Connection connection = null;
- Statement statement = null;
- ResultSet resultSet = null;
- try {
- // 1.加载驱动//com.mysql.jdbc.Driver
- /*
- * DriverManager.registerDriver(new
- * Driver());用这种方法会加载两次驱动,也就是说会创建两个drive对象
- */
- Class.forName("com.mysql.jdbc.Driver");
- // 2.获取连接
- connection = DriverManager.getConnection(url, user, password);
- // 3.获取用于向数据库发送SQL的Statement对象
- statement = connection.createStatement();
- // 4.执行sql,获取数据
- resultSet = statement.executeQuery("SELECT * FROM users;");
- // 解析数据
- while (resultSet.next()) {
- int id = resultSet.getInt("id");
- String name = resultSet.getString("name");
- String psd = resultSet.getString("password");
- String email = resultSet.getString("email");
- String birthday = resultSet.getString("birthday");
- System.out.println(id + " " + name + " " + psd + " " + email
- + " " + birthday);
- }
- } catch (ClassNotFoundException e) {
- e.printStackTrace();
- } catch (SQLException e) {
- e.printStackTrace();
- } finally {
- //5.关闭连接,释放资源
- if (resultSet != null) {
- try {
- resultSet.close();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- resultSet = null;
- }
- if (statement != null) {
- try {
- statement.close();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- statement = null;
- }
- if (connection != null) {
- try {
- connection.close();
- } catch (SQLException e) {
- // TODO Auto-generated catch block
- e.printStackTrace();
- }
- connection = null;
- }
- }
二、使用连接池连接
·在tomcat的config目录下,编辑context.xml,添加
<Resource name="jdbc/mysqlds"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="root"
password=""
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/数据库名?useUnicode=true&characterEncoding=utf-8" />
·在项目中创建一个类,我的类名是DbHelper.java
package com.tyut;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
public class DbHelper {
public static QueryRunner getQueryRunner() {
DataSource ds = null;
try {
// 获取数据源对象
Context context = new InitialContext();
ds = (DataSource) context.lookup("java:/comp/env/jdbc/mysqlds");
} catch (Exception e) {
e.printStackTrace();
}
QueryRunner qr = new QueryRunner(ds);
return qr;
}
}
·在servlet中调用类方法
QueryRunner qr = DbHelper.getQueryRunner();
qr.query(sql,new BeanListHandler(封装类.class)); //查询sql并封装类
qr.update(sql); //更新sql