简单的增删改查操作
基本步骤:
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);
}
}
}