CRUD是最常见的数据库操作,即增删改查
C 增加(Create)
R 读取查询(Retrieve)
U 更新(Update)
D 删除(Delete)
在JDBC中增加,删除,修改的操作都很类似,只是传递不同的SQL语句就行了。
C 增加(Create)
R 读取查询(Retrieve)
U 更新(Update)
D 删除(Delete)
在JDBC中增加,删除,修改的操作都很类似,只是传递不同的SQL语句就行了。
- 增加
用于向数据库中插入数据
package jdbc; import java.sql.Connection; import java.sql.DriverManager; 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(); } try ( Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin"); Statement s = c.createStatement(); ) { String sql = "insert into hero values(null," + "'提莫'" + "," + 313.0f + "," + 50 + ")"; s.execute(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
- 删除
删除和增加很类似,只不过是执行的SQL语句不一样罢了
package jdbc; import java.sql.Connection; import java.sql.DriverManager; 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(); } try ( Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin"); Statement s = c.createStatement(); ) { String sql = "delete from hero where id = 5"; s.execute(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
- 修改
修改也一样,执行另一条SQL语句就可以了
package jdbc; import java.sql.Connection; import java.sql.DriverManager; 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(); } try ( Connection c = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8", "root", "admin"); Statement s = c.createStatement(); ) { String sql = "update hero set name = 'name 5' where id = 3"; s.execute(sql); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
- 查询语句
executeQuery 执行SQL查询语句
注意: 在取第二列的数据的时候,用的是rs.get(2) ,而不是get(1). 这个是整个Java自带的api里唯二的地方,使用基1的,即2就代表第二个。
另一个地方是在PreparedStatement这里
package jdbc; import java.sql.Connection; 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) { 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"); Statement s = c.createStatement();) { String sql = "select * from hero"; // 执行查询语句,并把结果集返回给ResultSet ResultSet rs = s.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id");// 可以使用字段名 String name = rs.getString(2);// 也可以使用字段的顺序 float hp = rs.getFloat("hp"); int damage = rs.getInt(4); System.out.printf("%d\t%s\t%f\t%d%n", id, name, hp, damage); } // 不一定要在这里关闭ReultSet,因为Statement关闭的时候,会自动关闭ResultSet // rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
- SQL语句判断账号密码是否正确
1. 创建一个用户表,有字段name,password
2. 插入一条数据
insert into user values(null,'dashen','thisispassword');
3. SQL语句判断账号密码是否正确
判断账号密码的正确方式是根据账号和密码到表中去找数据,如果有数据,就表明密码正确了,如果没数据,就表明密码错误。
不恰当的方式 是把uers表的数据全部查到内存中,挨个进行比较。 如果users表里有100万条数据呢? 内存都不够用的。
CREATE TABLE user ( id int(11) AUTO_INCREMENT, name varchar(30) , password varchar(30), PRIMARY KEY (id) ) ; insert into user values(null,'dashen','thisispassword');
package jdbc; import java.sql.Connection; 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) { 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"); Statement s = c.createStatement(); ) { String name = "dashen"; //正确的密码是:thisispassword String password = "thisispassword1"; String sql = "select * from user where name = '" + name +"' and password = '" + password+"'"; // 执行查询语句,并把结果集返回给ResultSet ResultSet rs = s.executeQuery(sql); if(rs.next()) System.out.println("账号密码正确"); else System.out.println("账号密码错误"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
- 获取总数
执行的sql语句为
select count(*) from hero
然后通过ResultSet获取出来
package jdbc; import java.sql.Connection; 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) { 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"); Statement s = c.createStatement();) { String sql = "select count(*) from hero"; ResultSet rs = s.executeQuery(sql); int total = 0; while (rs.next()) { total = rs.getInt(1); } System.out.println("表Hero中总共有:" + total+" 条数据"); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } }
- 分页查询
设计一个方法,进行分页查询
public static void list(int start, int count)
start 表示开始页数,count表示一页显示的总数
list(0,5) 表示第一页,一共显示5条数据
list(10,5) 表示第三页,一共显示5条数据
package jdbc; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestJDBC { public static void list(int start, int count){ 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"); Statement s = c.createStatement();) { String sql = "select * from hero limit " +start + "," + count; // 执行查询语句,并把结果集返回给ResultSet ResultSet rs = s.executeQuery(sql); while (rs.next()) { int id = rs.getInt("id");// 可以使用字段名 String name = rs.getString(2);// 也可以使用字段的顺序 float hp = rs.getFloat("hp"); int damage = rs.getInt(4); System.out.printf("%d\t%s\t%f\t%d%n", id, name, hp, damage); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } public static void main(String[] args) { list(10,5); } }