新建一个用户表:用户id 用户名 密码
新建一个角色表:角色id 角色名——sql初始化几个角色,员工 管理员 组长等
新建一个角色用户中间表:1个用户可以有多个角色 1个角色可以对应多个用户
主键 用户id 角色id
功能:
新增用户表信息,
查询管理员角色的id,用户新增返回的主键
给用户初始化一个角色:管理员——入库到数据中
package com.jdbc.day04.utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
/**
* @Author Jin XueYang
* @Date 2022/3/22
*/
public class SqlDataUtils {
public static String url = "jdbc:mysql://localhost:3307/schooldb?useSSL=false&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf-8";
public static String username = "root";
public static String pwd = "123456";
//加载驱动,静态代码块只执行一次,驱动加载一次就可以
static {
try {
Properties properties = new Properties();
InputStream resourceAsStream = SqlDataUtils.class.getResourceAsStream("db.properties");
properties.load(resourceAsStream);
String classname = properties.get("db.className").toString();
url = properties.get("db.url").toString();
username = properties.get("db.username").toString();
pwd = properties.get("db.password").toString();
Class.forName(classname);
} catch (ClassNotFoundException | IOException e) {
e.printStackTrace();
}
}
//获取数据库连接
public Connection getCon() throws SQLException {
return DriverManager.getConnection(url, username, pwd);
}
public int exUpdate(String sql, Object... obj) {
Connection con = null;
PreparedStatement pt = null;
ResultSet res = null;
try {
con = this.getCon();
pt = con.prepareStatement(sql);
for (int i = 1; i <= obj.length; i++) {
pt.setObject(i, obj[i - 1]);
}
int i = pt.executeUpdate();
return i;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
this.close(res, pt, con);
}
return -1;
}
//仅仅返回新增主键而服务
public int insertForkey(String sql, Object... obj) {
Connection con = null;
PreparedStatement pt = null;
ResultSet res = null;
try {
con = this.getCon();
pt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
for (int i = 1; i <= obj.length; i++) {
pt.setObject(i, obj[i - 1]);
}
int i = pt.executeUpdate();
res = pt.getGeneratedKeys();
if (res.next()) {
return res.getInt(1);
}
return 0;//代表新增成功但是没有拿到主键
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
this.close(res, pt, con);
}
return -1;//代表失败
}
public List exQuery(String sql, Object... obj) {
Connection con = null;
PreparedStatement pt = null;
ResultSet res = null;
try {
con = this.getCon();
pt = con.prepareStatement(sql);
for (int i = 1; i <= obj.length; i++) {
pt.setObject(i, obj[i - 1]);
}
res = pt.executeQuery();
ResultSetMetaData metaData = res.getMetaData();
int columnCount = metaData.getColumnCount();
List<Object> list = new ArrayList<>();
while (res.next()) {
Map<Object, Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
map.put(metaData.getColumnName(i), res.getObject(i));
}
list.add(map);
}
return list;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
this.close(res, pt, con);
}
return null;
}
//关闭资源
public void close(ResultSet result, PreparedStatement state, Connection con) {
try {
if (result != null) {
result.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (con != null) {
con.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (state != null) {
state.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
package com.jdbc.day03;
import com.jdbc.day03.utils.SqlDataUtils;
import java.util.List;
import java.util.Map;
/**
* @Author Jin XueYang
* @Date 2022/3/2
*/
public class userRole {
public static void main(String[] args) {
SqlDataUtils metaDataUtils = new SqlDataUtils();
String sql = "insert into user1(username,password) values(?,?)";
int key = metaDataUtils.insertForkey(sql,"张三","ascdf123");
String sqlRole = "select roleid from role1 where rolename = 'admin'";
List<Map> list = metaDataUtils.exQuery(sqlRole);
if(list!=null&&list.size()>0){
Map roleMap = list.get(0);
Object roleid = roleMap.get("roleid");
System.out.println(roleid);
System.out.println(key);
String sqlMiddle = "insert into user_role1 values(null,?,?)";
metaDataUtils.exUpdate(sqlMiddle,key,roleid);
}
}
}