黑马程序员技术交流社区

标题: java + sql 基本操作 [打印本页]

作者: mimawo 时间: 2014-12-28 16:53
标题: 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.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("更新失败");
}
}
}

作者: junge520 时间: 2014-12-28 17:26
很实用的东西，感谢lz

作者: duluhua 时间: 2014-12-28 19:18
谢谢楼主，受教了

作者: 小僧玄奘 时间: 2014-12-28 23:44
JdbC。。。。

作者: 些许 时间: 2014-12-28 23:55
过来学习哈发现看不懂啊

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2