【⒈ 连接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.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://localhost: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.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://localhost: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.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://localhost: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.SQLServerDriver");
con = DriverManager.getConnection("jdbc:sqlserver://localhost: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;
this.name = 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) {
this.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.getSex());
}
}
}
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("更新失败");
}
}
}
|
|