实验九   使用JDBC完成数据的增删改查

一、实验目的

1. 熟练JDBC的基本概念和原理；

2. 掌握使用JDBC进行数据库连接和操作的方法；

3. 熟悉JDBC在增删改查中的应用；

二、实验内容

在实际项目的开发中，用户信息是存放在数据库中的，管理员对用户信息进行管理的过程，无时无刻不涉及到增删改查操作。本次实验要求创建数据库表user表，包括属性id（主键）、name、password、email、birthday等字段，其中id为学号，创建数据库表后使用JDBC实现对数据库中用户信息的增加（JdbcInsertTest.java）、删除（DeleteUserTest.java）、修改（UpdateUserTest.java）和查询（FindAllUserTest.java和FindUserByIdTest.java）操作，要求数据库表中有一行信息为学生本人的学号姓名。

三、实验结果（源代码、运行截图）

源代码

User.java:

<code>import java.util.Date;

public class User {

private int id;

private String name;

private String password;

private String email;

private Date birthday;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getUsername() {

return name;

}

public void setUsername(String username) {

this.name = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Date getBirthday() {

return birthday;

}

public void setBirthday(Date birthday) {

this.birthday = birthday;

}

}

JDBCUtil.java:

import java.sql.*;

public class JDBCUtil {

public static Connection getConnection() throws SQLException,

ClassNotFoundException{

Class.forName("com.mysql.cj.jdbc.Driver");

String url="jdbc:mysql://localhost:3306/javatest9?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";code>

String username="root";code>

String password="123456";code>

Connection conn= DriverManager.getConnection(url,username,password);

return conn;

}

public static void release(Statement stmt,Connection conn){

if(stmt!=null){

try {

stmt.close();

}catch (SQLException e){

e.printStackTrace();

}

stmt=null;

}

if(conn!=null){

try {

conn.close();

}catch (SQLException e){

e.printStackTrace();

}

conn=null;

}

}

public static void release(ResultSet rs,Statement stmt,Connection conn){

if(rs!=null){

try {

rs.close();

}catch (SQLException e){

e.printStackTrace();

}

rs=null;

}

release(stmt,conn);

}

}

UserDao.java:

import java.sql.Array;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Statement;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

public class UserDao {

//添加用户

public boolean insert(User user){

Connection conn=null;

Statement stmt=null;

ResultSet rs=null;

try {

conn=JDBCUtil.getConnection();

stmt=conn.createStatement();

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

String birthday=sdf.format(user.getBirthday());

String sql="INSERT INTO user(id,name,password,email,birthday)"+code>

"VALUES("

+user.getId()

+",'"

+user.getUsername()

+"','"

+user.getPassword()

+"','"

+user.getEmail()

+"','"

+ birthday+"')";

int num=stmt.executeUpdate(sql);

if(num>0){

return true;

}

return false;

}catch (Exception e){

e.printStackTrace();

}finally {

JDBCUtil.release(rs,stmt,conn);

}

return false;

}

//查询操作

public ArrayList<User> findAll(){

Connection conn=null;

Statement stmt=null;

ResultSet rs=null;

ArrayList<User> list=new ArrayList<User>();

try {

conn=JDBCUtil.getConnection();

stmt=conn.createStatement();

String sql="SELECT * FROM user";code>

rs=stmt.executeQuery(sql);

while(rs.next()){

User user=new User();

user.setId(rs.getInt("id"));

user.setUsername(rs.getString("name"));

user.setPassword(rs.getString("password"));

user.setEmail(rs.getString("email"));

user.setBirthday(rs.getDate("birthday"));

list.add(user);

}

return list;

}catch (Exception e){

e.printStackTrace();

}finally {

JDBCUtil.release(rs,stmt,conn);

}

return null;

}

//查找指定id操作

public User find(int id){

Connection conn=null;

Statement stmt=null;

ResultSet rs=null;

try {

conn=JDBCUtil.getConnection();

stmt=conn.createStatement();

String sql="SELECT * FROM user WHERE id="+id;code>

rs=stmt.executeQuery(sql);

while(rs.next()){

User user=new User();

user.setId(rs.getInt("id"));

user.setUsername(rs.getString("name"));

user.setPassword(rs.getString("password"));

user.setEmail(rs.getString("email"));

user.setBirthday(rs.getDate("birthday"));

return user;

}

return null;

}catch (Exception e){

e.printStackTrace();

}finally {

JDBCUtil.release(rs,stmt,conn);

}

return null;

}

//删除操作

public boolean delete(int id){

Connection conn=null;

Statement stmt=null;

ResultSet rs=null;

try{

conn=JDBCUtil.getConnection();

stmt=conn.createStatement();

String sql="DELETE FROM user WHERE id="+id;code>

int num=stmt.executeUpdate(sql);

if(num>0){

return true;

}

return false;

}catch (Exception e){

e.printStackTrace();

}finally {

JDBCUtil.release(rs,stmt,conn);

}

return false;

}

//修改用户

public boolean update(User user){

Connection conn=null;

Statement stmt=null;

ResultSet rs=null;

try {

conn=JDBCUtil.getConnection();

stmt=conn.createStatement();

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");

String birthday=sdf.format(user.getBirthday());

String sql="UPDATE user set name='"+user.getUsername()code>

+"',password='"+user.getPassword()+"',email='"

+user.getEmail()+"',birthday='"+birthday

+"'WHERE id="+user.getId();

int num=stmt.executeUpdate(sql);

if(num>0){

return true;

}

return false;

}catch (Exception e){

e.printStackTrace();

}finally {

JDBCUtil.release(rs,stmt,conn);

}

return false;

}

}

JdbcInsertTest.java:

import java.util.Calendar;

import java.util.Date;

public class JdbcInsertTest {

public static void main(String[] args) {

UserDao ud=new UserDao();

User user=new User();

user.setId(202266623);

user.setUsername("李四");code>

user.setPassword("123");

user.setEmail("lisi@qq.com");

Calendar calendar= Calendar.getInstance();

calendar.set(2003, Calendar.FEBRUARY, 21); //年月日 也可以具体到时分秒如calendar.set(2015, 10, 12,11,32,52);

Date date=calendar.getTime();//date就是你需要的时间

user.setBirthday(date);

boolean b=ud.insert(user);

System.out.println(b);

}

}

DeleteUserTest.java:

public class DeleteUserTest {

public static void main(String[] args) {

UserDao userDao=new UserDao();

boolean b=userDao.delete(202233366);

System.out.println(b);

}

}

UpdateUserTest.java:

import java.util.Calendar;

import java.util.Date;

public class UpdateUserTest {

public static void main(String[] args) {

UserDao userDao=new UserDao();

User user=new User();

user.setId(202233366);

user.setUsername("赵六");

user.setPassword("12345");

user.setEmail("zhaoliu@qq.com");

Calendar calendar= Calendar.getInstance();

calendar.set(2003, Calendar.JUNE, 21); //年月日 也可以具体到时分秒如calendar.set(2015, 10, 12,11,32,52);

Date date=calendar.getTime();//date就是你需要的时间

user.setBirthday(date);

boolean b=userDao.update(user);

System.out.println(b);

}

}

FindAllUserTest.java:

import java.util.ArrayList;

public class FindAllUserTest {

public static void main(String[] args) {

UserDao userDao=new UserDao();

ArrayList<User> list=userDao.findAll();

for(int i=0;i<list.size();i++){

System.out.println("id:"+list.get(i).getId()+"\t"+"name:"+list.get(i).getUsername()+"\t\t"+"email:"+list.get(i).getEmail()+"\t\t"+"birthday:"+list.get(i).getBirthday());

}

}

}

FindUserByIdTest.java:

public class FindUserByIdTest {

public static void main(String[] args) {

UserDao userDao=new UserDao();

User user= userDao.find(202233366);

System.out.println("id:"+user.getId()+"\tname:"+user.getUsername()+"\t\temail:"+user.getEmail()+"\t\tbirthday:"+user.getBirthday());

}

}

运行截图

MySQL命令窗查看user表

整体代码框架(IDEA和MySQL连接成功)

删除用户数据(id:202233366 name:赵六 email:zhaoliu@qq.com birthday:2003-6-21)

增加用户数据(id:202233366 name:赵六 email:zhaoliu@qq.com birthday:2003-6-21)

修改用户数据(id:202233366 name:赵六 email:zhaoliu@qq.com birthday:2003-6-21改为id:202233366 name:王五 email:wangwu@qq.com birthday:2004-6-21)

查询用户数据(查看所有)

查询用户数据(查看指定id用户数据，这里查询202233366)