java-jdbc-mysql:实现数据库表的增删改查

以数据库test下数据表student(sno,sname,ssex,sage,sdept)为例:

student表中的已有的所有记录:

技术分享

Java代码对表test.student的操作:

创建student类,包含String sno,String sname,String ssex,int sage,String sdept:

 1 package jdbcTest;
 2 
 3 public class Student {
 4     private String sno,sname,ssex,sdept;  //学号,姓名,性别,部门
 5     private int sage;  //年龄
 6     
 7     public Student(){}
 8     
 9     public Student(String sno,String sname,String sex,String dept,int age){
10         this.sno = sno;
11         this.sname = sname;
12         this.ssex = sex;
13         this.sdept=dept;
14         this.sage = age;
15     }
16     
17     public void setSno(String sno){
18         this.sno = sno;
19     }
20     
21     public String getSno(){
22         return this.sno;
23     }
24     
25     public void setSname(String sname){
26         this.sname = sname;
27     }
28     
29     public String getSname(){
30         return this.sname;
31     }
32     
33     public void setSsex(String sex){
34         this.ssex = sex;
35     }
36     
37     public String getSsex(){
38         return this.ssex;
39     }
40     
41     public void setSdept(String dept){
42         this.sdept = dept;
43     }
44     
45     public String getSdept(){
46         return this.sdept;
47     }
48     
49     public void setSage(int age){
50         this.sage = age;
51     }
52     
53     public int getSage(){
54         return this.sage;
55     }
56     
57     public String toString(){
58         return this.sno+" "+this.sname+" "+this.ssex+" "+this.sage+" "+this.sdept;
59     }
60 }

创建StudentDA类,用于数据库连接、操作数据库:

  1 package jdbcTest;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 import java.sql.Statement;
  9 import java.util.ArrayList;
 10 import java.util.List;
 11 
 12 public class StudentDA {
 13     String driverName = "com.mysql.jdbc.Driver"; // 加载JDBC驱动
 14     String dbURL = "jdbc:mysql://localhost:3306/test"; // 连接服务器和数据库sample
 15     String userName = "test"; // 数据库用户名
 16     String userPwd = "test"; // 数据库密码
 17     static Connection dbConn = null;  //连接信息
 18     Statement statement;   //sql语句
 19     PreparedStatement pstatement;
 20     ResultSet rs;      //结果集
 21     
 22     //数据库连接
 23     public Connection dBConnection(){
 24         try {
 25             Class.forName(driverName);
 26             dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
 27             System.out.println("Connection Succeed!");             
 28         } catch (Exception e) {
 29             System.out.println("Connection failed!");
 30             e.printStackTrace();
 31         }
 32         return dbConn;
 33     }
 34     
 35     //查询记录,将查询结果放在list中
 36     public List<Student> selectAllStudent(){
 37         List<Student> studentList = new ArrayList<Student>(); //存放查询结果
 38         String sql = "select * from student"; 
 39         
 40         try{
 41             statement = dbConn.createStatement();
 42             rs = statement.executeQuery(sql);  //执行sql语句并返回结果集
 43             while (rs.next()) {
 44                 Student student = new Student();
 45                 student.setSno(rs.getString("sno"));
 46                 student.setSname(rs.getString("sname"));
 47                 student.setSsex(rs.getString("ssex"));
 48                 student.setSdept(rs.getString("sdept"));
 49                 student.setSage(rs.getInt("sage"));
 50                 studentList.add(student);  //将查询出的student信息放入studentList链表中
 51             }  
 52         }catch(Exception e){
 53             e.printStackTrace();
 54         }
 55 /*        finally{
 56             try {
 57                 statement.close();
 58                 dbConn.close();
 59             } catch (SQLException e) {
 60                 // TODO Auto-generated catch block
 61                 e.printStackTrace();
 62             }
 63         }*/
 64         return studentList;        
 65     }
 66     
 67     //插入一个学生记录,如果插入成功,返回true
 68     public boolean insertStudent(Student stu){
 69         int i = 0; 
 70         String sqlInset = "insert into test.student(sno,sname,ssex,sage,sdept)" +
 71     " values(?, ?, ?, ?,?)";   //插入语句
 72          
 73          try {
 74             pstatement = dbConn.prepareStatement(sqlInset);
 75             pstatement.setString(1, stu.getSno()); //设置SQL语句第一个“?”的值
 76             pstatement.setString(2,stu.getSname());
 77             pstatement.setString(3,stu.getSsex());
 78             pstatement.setInt(4, stu.getSage());
 79             pstatement.setString(5,stu.getSdept());
 80             i = pstatement.executeUpdate();  //执行插入数据操作,返回影响的行数
 81             if(i == 1){       //如果插入数据成功,则影响的行数为1
 82                 return true;
 83             }
 84         } catch (SQLException e) {
 85             // TODO Auto-generated catch block
 86             e.printStackTrace();
 87         }
 88         return false;
 89     }
 90     
 91     //根据sno删除记录
 92     public boolean deleteStudent(String sno){
 93         int i = 0; 
 94         String sqlDelete = "delete from test.student where sno = " + sno; 
 95         
 96         try {
 97             pstatement = dbConn.prepareStatement(sqlDelete);
 98             i = pstatement.executeUpdate();  //执行删除数据操作,返回影响的行数
 99             if(i == 1){       //如果删除数据成功,则影响的行数为1
100                 return true;
101             }
102         } catch (SQLException e) {
103             // TODO Auto-generated catch block
104             e.printStackTrace();
105         }
106         return false;
107     }
108     
109     //删除所有的记录
110     public void deleteAll(){
111         String sqlDeleteAll = "delete from student";
112         try {
113             pstatement = dbConn.prepareStatement(sqlDeleteAll);
114         } catch (SQLException e) {
115             // TODO Auto-generated catch block
116             e.printStackTrace();
117         }
118     }
119     
120     //根据学号sno,更新学生年龄sage
121     public boolean updateStudent(String sno,int age){
122         int i =0;
123         String sqlUpdate = "update test.student set sage=? where sno=?";
124         try {
125             pstatement = dbConn.prepareStatement(sqlUpdate);
126             pstatement.setInt(1,age); //设置SQL语句第一个"?"的参数值
127             pstatement.setString(2, sno); 
128             i = pstatement.executeUpdate(); //执行修改操作,返回影响的行数
129             if(i == 1){          //修改成功返回true
130                 return true;
131             }
132         } catch (SQLException e) {
133             // TODO Auto-generated catch block
134             e.printStackTrace();
135         }    
136         return false;        
137     }
138 
139     //测试增删改查
140     public static void main(String args[]){
141         StudentDA stuDA = new StudentDA();
142         dbConn = stuDA.dBConnection(); 
143         //测试查询
144         System.out.println("测试----查询所有记录 ");
145         List<Student> stuList = stuDA.selectAllStudent();
146         for(Student stu:stuList){
147             System.out.println(stu.toString());
148         }
149 
150 /*        //测试插入
151         System.out.println("测试----插入一条记录 ");
152         Student stu = new Student("200215334","test2","男","CS",25);
153         if(stuDA.insertStudent(stu)){
154             System.out.println("插入成功");
155         }else{
156             System.out.println("插入失败");
157         }
158         
159         //测试根据sno删除记录
160         if(stuDA.deleteStudent("200215334")){
161             System.out.println("删除成功");
162         }else{
163             System.out.println("删除失败");
164         }
165         
166         //测试更新
167         if(stuDA.updateStudent("200215333", 33)){
168             System.out.println("更新成功");
169         }else{
170             System.out.println("更新失败");
171         }*/
172     }
173 }

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。