java操作mysql中的表格_java-jdbc-mysql：实现数据库表的增删改查

1 packagejdbcTest;2

3 importjava.sql.Connection;4 importjava.sql.DriverManager;5 importjava.sql.PreparedStatement;6 importjava.sql.ResultSet;7 importjava.sql.SQLException;8 importjava.sql.Statement;9 importjava.util.ArrayList;10 importjava.util.List;11

12 public classStudentDA {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 publicConnection 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 returndbConn;33 }34

35 //查询记录,将查询结果放在list中

36 public ListselectAllStudent(){37 List studentList = new ArrayList(); //存放查询结果

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 = newStudent();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 block61 e.printStackTrace();62 }63 }*/

64 returnstudentList;65 }66

67 //插入一个学生记录,如果插入成功，返回true

68 public booleaninsertStudent(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 booleandeleteStudent(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 voiddeleteAll(){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,intage){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 voidmain(String args[]){141 StudentDA stuDA = newStudentDA();142 dbConn =stuDA.dBConnection();143 //测试查询

144 System.out.println("测试----查询所有记录 ");145 List 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 }