案例Java+sql基本操作

【⒈连接SQLServer数据库】package db;import java.sql.*;import java.util.*;import entity.Student;public class DBUtil {private Connection con;private PreparedStatement pstmt;private ResultSet rs;private String sql;//连接并且查询数据public ArrayList selectAllStudent(){sql = "select * from student";ArrayList arr = new ArrayList();try {Class.forName("com.microsoft.sqlserver.jdbc.SQ LServerDriver");con = DriverManager.getConnection("jdbc:sqlserver://loc alhost:1433;databasename=myschool", "sa", "");pstmt = con.prepareStatement(sql);rs = pstmt.executeQuery();while(rs.next()){Student s = new Student(rs.getInt("id"),rs.getString("name"),rs.getString("sex"));arr.add(s);}return arr;} catch (ClassNotFoundException e) {e.printStackTrace();return null;}catch (SQLException e) {e.printStackTrace();return null;}finally{try {if(rs!=null)rs.close();if(pstmt != null)pstmt.close();if(con!=null && !con.isClosed())con.close();}catch (SQLException e) {e.printStackTrace();}}}//连接并且插入数据public int insertStudent(Student s){sql ="insert into student(name,sex) values(?,?)";try {Class.forName("com.microsoft.sqlserver.jdbc.SQL ServerDriver");con = DriverManager.getConnection("jdbc:sqlserver://loc alhost:1433;databasename=myschool","sa", "");pstmt = con.prepareStatement(sql);pstmt.setString(1,s.getName());pstmt.setString(2,s.getSex());return pstmt.executeUpdate();}catch (ClassNotFoundException e) {e.printStackTrace();return 0;}catch (SQLException e) {e.printStackTrace();return 0;}finally{try {if(pstmt != null)pstmt.close();if(con!=null && !con.isClosed())con.close();}catch (SQLException e) {e.printStackTrace();}}}//连接并且删除数据public int deleteStudentById(int id){sql ="delete from student where id=?";try {Class.forName("com.microsoft.sqlserver.jdbc.SQ LServerDriver");con = DriverManager.getConnection("jdbc:sqlserver://loc alhost:1433;databasename=myschool","sa", "");pstmt = con.prepareStatement(sql);pstmt.setInt(1,id);return pstmt.executeUpdate();}catch (ClassNotFoundException e) {e.printStackTrace();return 0;}catch (SQLException e) {e.printStackTrace();return 0;}finally{try {if(pstmt != null)pstmt.close();if(con!=null && !con.isClosed())con.close();}catch (SQLException e) {e.printStackTrace();}}}//连接并且更新数据public int updateStudent(Student s){sql ="update student set name=?,sex=? where id=?"; try {Class.forName("com.microsoft.sqlserver.jdbc.S QLServerDriver");con = DriverManager.getConnection("jdbc:sqlserver://loc alhost:1433;databasename=myschool","sa", "");pstmt = con.prepareStatement(sql);pstmt.setString(1,s.getName());pstmt.setString(2,s.getSex());pstmt.setInt(3,s.getId());return pstmt.executeUpdate();} catch (ClassNotFoundException e) {e.printStackTrace();return 0;} catch (SQLException e) {e.printStackTrace();return 0;}finally{try {if(pstmt != null)pstmt.close();if(con!=null && !con.isClosed())con.close();} catch (SQLException e) {e.printStackTrace();}}}}【⒉模拟数据库中的数据并创建一实体个类entity】package entity;public class Student {private int id;private String name;private String sex;public Student() {super();}public Student(int id, String name, String sex) {super();this.id = id; = name;this.sex = sex;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) { = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}}【⒊创建测试类】1创建一个SelectTest查询类:package test;import java.util.ArrayList;import db.DBUtil;import entity.Student;public class SelectTest {/*** @param args*/public static void main(String[] args) {DBUtil db = new DBUtil();ArrayList arr = db.selectAllStudent();for(int i=0;i<arr.size();i++){Student s = (Student)arr.get(i);System.out.println(s.getId()+s.getName()+s.getS ex());}}}2创建一个insertTest插入类package test;import java.util.Scanner;import db.DBUtil;import entity.Student;public class insertTest {/*** @param args*/public static void main(String[] args) {DBUtil db = new DBUtil();Scanner input = new Scanner(System.in); System.out.print("please input name:");String name = input.next();System.out.print("please input sex:");String sex = input.next();Student s = new Student();s.setName(name);s.setSex(sex);int re = db.insertStudent(s);if(re == 1)System.out.println("插入成功");else{System.out.println("插入失败");}}}3创建一个DeleteTest删除数据类:package test;import db.DBUtil;public class DeleteTest {/*** @param args*/public static void main(String[] args) {DBUtil db = new DBUtil();int re = db.deleteStudentById(3);if(re == 1)System.out.println("删除成功");else{System.out.println("删除失败");}}}4创建一个UpdateTest数据更新类: package test;import db.DBUtil;import entity.Student;public class UpdateTest {/*** @param args*/public static void main(String[] args) { DBUtil db= new DBUtil();Student s = new Student(2,"ttttttttt","xxxx"); int re = db.updateStudent(s);if(re == 1)System.out.println("更新成功");else{System.out.println("更新失败");}}}。