JavaWeb学习笔记——jdbc连接数据库&CRUD

简单的增删改查操作

基本步骤：

1）数据库建表：MySQL数据库
2）eclipse新建项目
3）jdbc连接数据库：编写连接数据库工具类
4）实体类
4）增删查改

数据库建表

MySQL数据库javaweb中新建一个表，名称为user

CREATE TABLE user(
    id INT(10) PRIMARY KEY,
    name VARCHAR(20),
    age INT(4)
);

新建项目

这里我用的eclipse IDE。
新建一个工程，名为jdbcDemo，新建包；项目目录下新建名为lib的文件夹，用于存放mysql数据库驱动jar包。项目完成后整个项目目录如下图：

jdbc连接数据库

编写属性配置文件jdbc.properties

在src包下新建一个属性配置文件，名为jdbc.properties。该属性文件的作用主要是提供了连接数据库的url、用户名、密码等基本信息。

jdbc.properties中配置信息如下：

# driverClass
driverClass=com.mysql.jdbc.Driver
# url that connects to database. javaweb is database name.
url=jdbc\:mysql\://localhost\:3306/javaweb
# your mysql's username.
user=root
# your mysql's password.
password=root

在com.zrxjuly.util包下新建一个类，DBUtil.java。

package com.zrxjuly.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DBUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;

    static {
        //加载properties文件数据.
        //加载属性资源文件.
        ResourceBundle resourceBundle = ResourceBundle.getBundle("jdbc");
        driverClass = resourceBundle.getString("driverClass");
        url = resourceBundle.getString("url");
        user = resourceBundle.getString("user");
        password = resourceBundle.getString("password");
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //得到连接.
    public static Connection getConnection() throws Exception {
        return DriverManager.getConnection(url, user, password);
    }

    //关闭资源连接.
    public static void closeResource(ResultSet rs, Statement stmt, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            stmt = null;
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }

}

实体类

com.zrxjuly.entity中新建java文件：User.java。

package com.zrxjuly.entity;

/**
 * user实体类.
 */
public class User {
    private int id;
    private String name;
    private int age;

    //省略getter和setter方法，可通过快捷方式生成。

}

CRUD

com.zrxjuly.crud中新建Java文件：CRUDTest.java。
这里没有页面的操作，仅用junit来运行测试CRUD。

package com.zrxjuly.crud;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.zrxjuly.entity.User;
import com.zrxjuly.util.DBUtil;

public class CRUDTest {

    /**
     * 查询.
     */
    @Test
    public void testSelect() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from user");
            List<User> userList = new ArrayList<User>();
            while (rs.next()) {
                User u = new User();
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setAge(rs.getInt("age"));
                userList.add(u);
            }

            for (User user : userList) {
                System.out.println("id:" + user.getId() + " ,name:" + user.getName() + " ,age:" + user.getAge());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(rs, stmt, conn);
        }
    }

    /**
     * 添加.
     */
    @Test
    public void insertUser() {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            int insertSuccess = stmt.executeUpdate("insert into user values(8,'zrx',20)");
            if (insertSuccess > 0) {
                System.out.println("insert success!");
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
            System.out.println(e);
        } finally {
            DBUtil.closeResource(null, stmt, conn);
        }
    }

    /**
     * 修改.
     */
    @Test
    public void updateUser() {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            int updateSuccess = stmt.executeUpdate("update user set name='zrxJuly',age=19 where id=8");
            if (updateSuccess > 0) {
                System.out.println("update success!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(null, stmt, conn);
        }
    }

    /**
     * 删除.
     */
    @Test
    public void deleteUser() {
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            int deleteSuccess = stmt.executeUpdate("delete from user where id=7");
            if (deleteSuccess > 0) {
                System.out.println("delete success!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResource(null, stmt, conn);
        }
    }
}