JDBC第2天(MySQL第8天)中的demo小练习
1.JDBC中预编译的sql执行对象(以变量的形式插入数据)
Scanner sc = new Scanner(System.in);
System.out.println("请输入姓名:");
String name = sc.nextLine();
System.out.println("请输入年龄:");
int age = sc.nextInt();
try (Connection conn = DBUtils.getConn()) {
// 创建sql语句
String sql = "insert into person values(null,?,?)";
// 创建预编译的sql执行对象
PreparedStatement stat = conn.prepareStatement(sql);
// 替换?为真正的变量
stat.setString(1, name);
stat.setInt(2, age);
// 执行sql语句
stat.executeUpdate();
System.out.println("保存成功!");
} catch (Exception e) {
e.printStackTrace();
}
sc.close();
2.JDBC中sql注入
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String username = sc.nextLine();
System.out.println("请输入密码:");
String password = sc.nextLine();
sc.close();
try (Connection conn = DBUtils.getConn()) {// JDK1.7之后推出的流自动关闭特性
// 创建sql执行对象
Statement stat = conn.createStatement();
// 执行sql语句
String sql = "select count(*) from user where username='" + username + "' and password='" + password + "'";
System.out.println(sql);
/**
*
* 请输入用户名: jsjgsjgjs
* 请输入密码:
* ' or '1'='1//这里注入了sql语句
* select count(*) from user where username='jsjgsjgjs' and password='' or '1'='1'
* 登录成功!
*/
ResultSet rs = stat.executeQuery(sql);
while (rs.next()) {
int count = rs.getInt("count(*)");
if (count > 0) {
System.out.println("登录成功!");
} else {
System.out.println("登录失败!");
}
}
} catch (Exception e) {
e.printStackTrace();
}
3.JDBC批量操作sql语句
try (Connection conn = DBUtils.getConn()) {
String sql = "insert into person values(null,?,?)";
PreparedStatement stat = conn.prepareStatement(sql);
// 批量插入100条数据 name1-100 年龄1-100
for (int i = 0; i <= 100; i++) {
stat.setString(1, "name" + i);
stat.setInt(2, i);
// 添加到批量操作
stat.addBatch();
// 避免内存溢出
if (i % 20 == 0) {// 每隔20次执行一次
stat.executeBatch();// 执行批量操作
}
}
stat.executeBatch();// 执行批量操作
System.out.println("数据插入成功!");
} catch (Exception e) {
e.printStackTrace();
}
4.JDBC分页查询
Scanner sc = new Scanner(System.in);
System.out.println("请输入页数:");
int page = sc.nextInt();
System.out.println("请输入条数:");
int count = sc.nextInt();
try (Connection conn = DBUtils.getConn()) {
String sql = "select name from person limit ?,?";
PreparedStatement stat = conn.prepareStatement(sql);
stat.setInt(1, (page - 1) * count);
stat.setInt(2, count);
ResultSet rs = stat.executeQuery();
while (rs.next()) {
String name = rs.getString("name");
System.out.println(name);
}
sc.close();
} catch (Exception e) {
e.printStackTrace();
}
5.JDBC事务处理
try (Connection conn = DBUtils.getConn()) {
// 创建sql执行对象
Statement stat = conn.createStatement();
// 开启事务,关闭自动提交
conn.setAutoCommit(false);
// 执行sql语句
String sql1 = "update hero set money=money-2000 where id=2";
String sql2 = "update hero set money=money+2000 where id=1";
stat.executeUpdate(sql1);
stat.executeUpdate(sql2);
// 查询剩余的钱是否大于等于0
String sql3 = "select money from hero where id=2";
ResultSet rs = stat.executeQuery(sql3);
while (rs.next()) {
int money = rs.getInt("money");
if (money >= 0) {// 说明钱够
conn.commit();// 提交事务
System.out.println("转账成功!");
} else {// 钱不够
conn.rollback();// 回滚
System.out.println("转账失败!");
}
}
} catch (Exception e) {
e.printStackTrace();
}
6.JDBC获取自增主键的值
Scanner sc = new Scanner(System.in);
System.out.println("请输入球队名称:");
String teamName = sc.nextLine();
System.out.println("请输入球员名称:");
String playerName = sc.nextLine();
try (Connection conn = DBUtils.getConn()) {
// 先查询是否存在这个球队
String sql = "select id from team where name=?";
PreparedStatement stat = conn.prepareStatement(sql);
stat.setString(1, teamName);// 通过球队名称查询球队id
ResultSet rs = stat.executeQuery();
int teamId = -1;// 上来先假设球队不存在,即球队的id不可能为-1
while (rs.next()) {// 若球队存在
System.out.println("该球队已存在!");
teamId = rs.getInt("id");
}
if (teamId == -1) {// 不存在这个球队
System.out.println("该球队不存在!");
// 保存该球队的数据
String sql1 = "insert into team values(null,?)";
PreparedStatement stat1 = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
stat1.setString(1, teamName);
stat1.executeUpdate();
System.out.println("球队添加成功!");
ResultSet rs1 = stat1.getGeneratedKeys();
while (rs1.next()) {
// 得到自增的球队id
teamId = rs1.getInt(1);
}
}
// 添加球员
String sql2 = "insert into player values(null,?,?)";
PreparedStatement stat2 = conn.prepareStatement(sql2);
stat2.setString(1, playerName);
stat2.setInt(2, teamId);
stat2.executeUpdate();
System.out.println("球员添加成功!");
sc.close();
} catch (Exception e) {
e.printStackTrace();
}
7.JDBC获取数据库和表及其字段的源数据
try (Connection conn = DBUtils.getConn()) {
// 得到数据库的源数据数据
DatabaseMetaData dbmd = conn.getMetaData();
System.out.println("数据库版本:" + dbmd.getDriverVersion());
System.out.println("用户名:" + dbmd.getUserName());
System.out.println("数据库地址:" + dbmd.getURL());
System.out.println("数据库厂商:" + dbmd.getDatabaseProductName());
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from emp");
// 得到表相关的源数据
ResultSetMetaData rsmd = rs.getMetaData();
// 得到表的字段数量
int count = rsmd.getColumnCount();
for (int i = 0; i < count; i++) {
System.out.println("字段名称:" + rsmd.getColumnName(i + 1));
System.out.println("字段类型:" + rsmd.getColumnTypeName(i + 1));
}
} catch (Exception e) {
e.printStackTrace();
}