java-jdbc-mysql：实现数据库表的增删改查
浏览数：23 /
时间：2015年06月12日
以数据库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 }
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
