- package com.模拟练习题十二;
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- /**
- * 2.按一下要求完成相应功能(12分) (不能用DBUtils等工具类)
- (1)设计一个工具类,定义一个方法名为getConnection的方法用来获取连接,相关的参数需要用形式参数的形式传入进去(4分)
- (2)有一个student如下
- id name age score
- 1 zs 18 98
- 2 ls 24 89
- 3 wwk 32 100
- 4 anni 25 54
- 将该表中每一条记录都封装成一个Student对象,(用a中的getConnection方法来获取连接
- 其中数据库名称,用户名和密码可以自定义)不能直接给Student赋值(不如:Student = new Student("2s",18.98)),要获取数据库
- 里面相应的内容,然后赋值给Student对象(4分)
- (3)将(2)中得到的Student对象存到list集合中并且用两种方式遍历出来(4分)
- (4)实现添加Student对象的方法
- public static int addStudent(Student student) {
- //要求传递一个Student对象,先判断该对象有没有,没有的话,添加,提示添加成功,有的话打印添加失败,并返回-1
- }
- (5)实现修改Student对象的方法
- public static int updateStudent(Student student) {
- //要求传递一个Student对象,先根据该对象id查询是否存在,有的话,进行修改操作,并提示修改成功,返回1,没有的话打印修改失败,并返回-1
- }
- (6)实现删除Student对象的方法
- public static int deleteStudentById(String id) {
- //根据id删除Student对象,删除成功返回1,删除失败返回-1
- }
- (7)实现获取所有Student对象的以下方法
- public static List<Student> getAllStudent1(){
- }
- public static List<Object[]> getAllStudent2(){
- //Object[]:中存储的是每个学生对象的信息,比如Object[] arr = {"zs",18,98}
- }
- public static List<Map<String,Object>> getAllStudent3(){
- //List<Map<String,Object>>:中存储的是Map集合,每个Map集合中存储的是该Student对象的属性值,其中key是String类型表示属性名称,
- //value为Object类型,代表该属性对应的值
- }
- * @author Administrator
- *
- */
- public class Demo02 {
- staticConnection con = JDBCUtils.getConnection();
- public static voidmain(String[] args)throws SQLException {
- List list = newArrayList<>();
- add(con,list);
- Student stu =newStudent("122",12,32);
- // System.out.println(addStudent(stu, list));
- // System.out.println(updateStudent(stu, con));
- // List<Object[]> list1 = getAllStudent2();List> list1 = getAllStudent3();
- for(Map map : list1) {
- for(String key :map.keySet()){
- System.out.print(key+" = "+ map.get(key)+" ");
- }
- System.out.println();
- }
- }
- public staticList> getAllStudent3() throws SQLException{
- //List<Map<String,Object>>:中存储的是Map集合,每个Map集合中存储的是该Student对象的属性值,其中key是String类型表示属性名称,List> list = newArrayList<>();
- //value为Object类型,代表该属性对应的值String sql = "select * from stu";
- PreparedStatement pstmt = con.prepareStatement(sql);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()){
- Map map = newHashMap<>();
- map.put("name", rs.getString("name"));
- map.put("age", rs.getInt("age"));
- map.put("socre", rs.getInt("socre"));
- list.add(map);
- }
- return list;
- }
- public staticList getAllStudent2() throws SQLException{
- //Object[]:中存储的是每个学生对象的信息,比如Object[] arr = {"zs",18,98}List list = newArrayList<>();
- String sql = "select * from stu";
- PreparedStatement pstmt = con.prepareStatement(sql);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()){
- Object[] a =newObject[3];
- a[0] = rs.getString("name");
- a[1] = rs.getInt("age");
- a[2] = rs.getInt("socre");
- list.add(a);
- }
- return list;
- }
- public staticList getAllStudent1(Connection con) throws SQLException{
- List list = newArrayList<>();
- String sql = "select * from stu";
- PreparedStatement pstmt = con.prepareStatement(sql);
- ResultSet rs = pstmt.executeQuery();
- while(rs.next()){
- Student s =new Student();
- s.setName(rs.getString("name"));
- s.setAge(rs.getInt("age"));
- s.setScore(rs.getInt("socre"));
- list.add(s);
- }
- return list;
- }
- /**
- * 数据库的修改
- * SQL语句的update 表名 set 列名=? where 类名=?
- * 遍历集合 将要添加的数据放在数据库中查找 没有就添加 有就返回-1
- * @param student
- * @param con
- * @return
- * @throws SQLException
- */
- public static intupdateStudent(Student student,Connection con)throws SQLException {
- //要求传递一个Student对象,先根据该对象id查询是否存在,有的话,进行修改操作,并提示修改成功,返回1,没有的话打印修改失败,并返回-1String name = student.getName();
- intage = student.getAge();
- intscore = student.getScore();
- String sql ="select * from stu where name=?";
- PreparedStatement sptmt = con.prepareStatement(sql);
- sptmt.setString(1, name);
- ResultSet rs = sptmt.executeQuery();
- if(!rs.next()){
- System.out.println("没有的话打印修改失败");
- return-1;
- }else{
- String sql1 = "update stu set name=? where name= ?";
- PreparedStatement pstmt = con.prepareStatement(sql1);
- pstmt.setString(1, "撒即可");
- pstmt.setString(2, name);
- pstmt.executeUpdate();
- return1;
- }
- }
- /**
- * 数据库的添加
- * SQL语句的insert into 表名 (列名,..) values (数据)
- * 遍历集合 将要添加的数据放在数据库中查找 没有就添加 有就返回-1
- * @param student
- * @param list
- * @return
- * @throws SQLException
- */
- public static intaddStudent(Student student,List list) throws SQLException{
- //要求传递一个Student对象,先判断该对象有没有,没有的话,添加,提示添加成功,有的话打印添加失败,并返回-1String name = student.getName();
- intage = student.getAge();
- intscore = student.getScore();
- for(Student stu : list){
- String sql = "select * from stu where name=? and age=? and socre=?";
- PreparedStatement pstmt = con.prepareStatement(sql);
- pstmt.setString(1, name);
- pstmt.setInt(2, age);
- pstmt.setInt(3, score);
- ResultSet rs = pstmt.executeQuery();
- //如果不存在 就添加
- if(!rs.next()) {
- String sql1 = "insert into stu (name ,age ,socre) values (?,?,?)";
- PreparedStatement pstmt1 = con.prepareStatement(sql1);
- pstmt1.setString(1, name);
- pstmt1.setInt(2, age);
- pstmt1.setInt(3, score);
- inttwo = pstmt1.executeUpdate();
- //判断是否添加成功 成功直接return 否则返回-1
- if(two != 0) {
- System.out.println("添加成功");
- return two;
- }
- }
- }
- System.out.println("添加失败");
- return-1;
- }
- /**
- * 将数据库中的数据添加到集合中 集合对象就是Student
- * @param con2
- * @param list
- * @throws SQLException
- */
- private static voidadd(Connection con2, List list) throws SQLException {
- //查询数据库String sql = "select * from stu";
- //发送SQL语句PreparedStatement pstmt = con.prepareStatement(sql);
- //解析SQL语句ResultSet rs = pstmt.executeQuery();
- //如果是符合条件 就添加
- while(rs.next()){
- Student s =new Student();
- s.setName(rs.getString("name"));
- s.setAge(rs.getInt("age"));
- s.setScore(rs.getInt("socre"));
- list.add(s);
- }
- }
- }
- class Student{
- private String name;
- private int age;
- private int score;
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public int getAge() {
- return age;
- }
- public voidsetAge(int age) {
- this.age = age;
- }
- public int getScore() {
- return score;
- }
- public voidsetScore(int score) {
- this.score = score;
- }
- publicStudent(String name,intage,int score) {
- super();
- this.name = name;
- this.age = age;
- this.score = score;
- }
- public Student() {
- super();
- // TODO Auto-generated constructor stub
- }
- @Override
- public String toString() {
- return"Student [name=" + name + ", age=" + age + ", score=" + score + "]";
- }
- }
来源: