发表于： 2019-10-14 19:43:11
1 904
今天做的事
获取自增长ID
在Statement通过execute或者executeUpdate执行完插入语句后，MySQL会为新插入的数据分配一个自增长id，(前提是这个表的id设置为了自增长,在Mysql创建表的时候，AUTO_INCREMENT就表示自增长)
package jdbc; import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement; public class TestJDBC { public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } String sql = "insert into hero values(null,?,?,?)"; try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin"); PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); ) { ps.setString(1, "盖伦"); ps.setFloat(2, 616); ps.setInt(3, 100); ps.execute(); ResultSet rs = ps.getGeneratedKeys(); if (rs.next()) { int id = rs.getInt(1); System.out.println(id); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }}
获取表的原数据
原数据就是和数据库服务器相关的数据，比如数据库版本，有哪些表，表有哪些字段，字段类型是什么等等
package jdbc; import java.sql.Connection;import java.sql.DatabaseMetaData;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement; public class TestJDBC { public static void main(String[] args) throws Exception { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } try (Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");) { DatabaseMetaData dbmd = c.getMetaData(); System.out.println("数据库产品名称:\t"+dbmd.getDatabaseProductName()); System.out.println("数据库产品版本:\t"+dbmd.getDatabaseProductVersion()); System.out.println("数据库和表分隔符:\t"+dbmd.getCatalogSeparator()); System.out.println("驱动版本:\t"+dbmd.getDriverVersion()); System.out.println("可用的数据库列表："); ResultSet rs = dbmd.getCatalogs(); while (rs.next()) { System.out.println("数据库名称:\t"+rs.getString(1)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }}
练习删除前一条数据
package jdbc;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 当插入一条数据之后，通过获取自增长id，得到这条数据的id，比如说是55. 删除这条数据的前一条，54. 如果54不存在，则删除53，以此类推直到删除上一条数据。 */public class Test { public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } String findAll = "select * from hero"; String add = "insert into hero values(null,?,?,?)"; try ( Connection c = DriverManager.getConnection( "jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin"); PreparedStatement ps = c.prepareStatement(add); Statement s = c.createStatement(); ){ ps.setString(1, "盖伦"); ps.setFloat(2, 1000); ps.setInt(3, 50); ps.execute(); System.out.println("插入盖伦成功！"); ResultSet r = ps.getGeneratedKeys(); int endID = 0; if (r.next()) { endID = r.getInt(1); } System.out.println("最后一条数据id = "+endID); System.out.println("\n增加后，表hero中有以下数据"); ResultSet r1 = s.executeQuery(findAll); while (r1.next()) { System.out.printf("%d\t%s\t%.0f\t%d\n", r1.getInt(1),r1.getString(2),r1.getFloat(3),r1.getInt(4)); } for (int i = endID-1; i > 0; i++) { int endIDFront = i; String find = String.format( "select id from Hero where id = %d",endIDFront); ResultSet r2 = s.executeQuery(find); / if (r2.next()) { System.out.printf("\n当id = %d存在时，删除此条数据\n",r2.getInt(1)); String delete = String.format( "delete from hero where id = %d", endIDFront); s.execute(delete); break; } } System.out.println("\n删除后，表hero中有以下数据"); ResultSet r3 = s.executeQuery(findAll); while (r3.next()) { System.out.printf("%d\t%s\t%.0f\t%d\n", r3.getInt(1),r3.getString(2),r3.getFloat(3),r3.getInt(4)); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } }} |
遇到的问题
无
收获
感觉写的有点啰嗦，改成do-while会不会更好
明天的计划
继续进行下一步
返回列表
评论