使用JDBC,完成对如下表的增删改查操作
增加操作
使用循环和随机数技巧,增加1000个数据。要求积分在0-200,注册时间均匀分布在2018年各个月份。从26个字母中随机取出3个字母作为昵称,昵称不能一样。ID自增;
删除操作
根据用户id进行删除操作;
修改操作
可以修改指定用户的积分;
查询操作
1.可以查出指定月份注册的用户;
2.根据ID查出指定用户的信息;
3.查出积分大于某个值的用户信息;
千万别忘了导入数据库驱动包
一、建立VIP表并插入上述三条数据;
create table vip(id int PRIMARY key auto_increment, name VARCHAR(30) unique, entry_date date ,point int , sex varchar(10) )
insert into vip values(1001,'tom',str_to_date('2018.3.14 13:00:00' ,'%Y.%m.%d %H:%i:%s' ),20,'男')
insert into vip values(1002,'seo',str_to_date('2018.4.2 23:21:20' ,'%Y.%m.%d %H:%i:%s' ),90,'女')
insert into vip values(1003,'ase',str_to_date('2018.5.1 21:10:05' ,'%Y.%m.%d %H:%i:%s' ),100,'男')
表效果:
select * from vip
二、分层:持久层(dao包),工具层(commons包),模型层(pojo包),业务层(serivce 包),测试层(test包);
1.在pojo包下创建实体类Vip
package com.bjsxt.pojo;
public class Vip {
private int id;
private String name;
private String entry_date;
private int point;
private String sex;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEntry_date() {
return entry_date;
}
public void setEntry_date(String entry_date) {
this.entry_date = entry_date;
}
public int getPoint() {
return point;
}
public void setPoint(int point) {
this.point = point;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
}
2.创建jdbc.properties文件
driver = com.mysql.jdbc.Driver
jdbcUrl = jdbc:mysql://localhost:3306/bjsxt?useUnicode=true&characterEncoding=utf-8
username = root
userpassword = mysql
3.在commons包下创建工具类;
package com.bjsxt.commons;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import com.bjsxt.pojo.Vip;
public class JdbcUtil {
private static String driver;
private static String jdbcUrl;
private static String username;
private static String userpassword;
//读取properties文件
static {
ResourceBundle bundle = ResourceBundle.getBundle("jdbc");
driver = bundle.getString("driver");
jdbcUrl = bundle.getString("jdbcUrl");
username = bundle.getString("username");
userpassword = bundle.getString("userpassword");
try {
//驱动注册
Class.forName(driver);//通过jdk反射机制将数据库驱动类实例化
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//获取Connection对象
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcUrl, username, userpassword);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
//关闭Statement
public static void closeStatement(Statement state) {
if(state!=null) {
try {
state.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//关闭Connection
public static void closeConnection(Connection conn) {
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}