JAVA实现对SQLServer增，删，改，查

JAVA实现对SQLServer增，删，改，查

 

package com.sql.test;
import java.sql.*;
import java.util.*;
import com.sql.form.UserInfo;;
public class UserSQLConn {

 /**
  * @param args
  * 实现增删改查
  */
 static String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
 static String dbUrl="jdbc:sqlserver://localhost:1433;DatabaseName=mydba";
 static String us="admin";
 static String pw="master";


 //连接数据库构造构造方法
 public  static Connection getConn(String dbDriver,String dbUrl,String us,String pw){
         Connection conn=null;
        try {
     Class.forName(dbDriver);
     conn=DriverManager.getConnection(dbUrl,us,pw);
   } catch (ClassNotFoundException  e) {
    e.printStackTrace();
   }catch(SQLException e1){
    e1.printStackTrace();
   }
   return conn;
 }


 //创建表
 public void dbCreate() throws SQLException {
  Connection conn = null;
  Statement stat = null;
  conn = getConn(driverName, dbUrl, us, pw);
  stat = conn.createStatement();
  stat
   .executeUpdate("create table UserInfo" +
   "(userId int," +
   "userName varchar(20)," +
   "userAddress varchar(20)," +
   "userAge int check(userAge between 0 and 150)," +
   "userSex varchar(20) default 'M' check(userSex='M' or userSex='W')" +
   ")");

 }


 //向表中添加数据
 public void addUser(ArrayList<UserInfo> ls) throws SQLException{
    Connection conn=getConn(driverName, dbUrl, us, pw);
    String insertSql="insert into UserInfo values(?,?,?,?,?);";
    PreparedStatement psta=conn.prepareStatement(insertSql);
    Iterator<UserInfo> it=ls.iterator();
    while(it.hasNext()){
     UserInfo uf=it.next();
    //设置表字段值
     psta.setInt(1, uf.getUserId());
     psta.setString(2, uf.getUserName());
     psta.setString(3, uf.getUserAddress());
     psta.setInt(4, uf.getUserAge());
     psta.setString(5, uf.getUserSex());
   //往数据库中增加一批数据
     psta.addBatch();
    }
    psta.executeBatch();
    psta.close();
    conn.close();
  
 }


 //查询表select 
 public void ddlSelect() throws SQLException{
   Connection conn=getConn(driverName, dbUrl, us, pw);
   Statement sta=conn.createStatement();
   ResultSet rs=sta.executeQuery("select * from UserInfo");
   while(rs.next()){
    int id=rs.getInt("userId");
    String name=rs.getString("userName");
    String addres=rs.getString("userAddress");
    int age=rs.getInt("userAge");
    String sex=rs.getString("userSex");
    System.out.println(id+"\t"+name+"\t"+addres+"\t"+age+"\t"+sex);
   }
   
 }


 //删除数据方法
 public void ddlDel(int index)throws SQLException{
   String ddlDelsql="delete from UserInfo where userId="+index;
   Connection conn=getConn(driverName, dbUrl, us, pw);
   Statement sta=conn.createStatement();
   sta.executeUpdate(ddlDelsql);
   sta.close();
   conn.close();
   
 }


 //修改方法
 public void ddlUpdate(String name,
     String Address,int age,String sex,int id)throws SQLException{
  String ddlUpSql="update UserInfo set userName=?,userAddress=?,userAge=?,userSex=? where userId=?";
  Connection conn=getConn(driverName, dbUrl, us, pw);
  PreparedStatement psta=conn.prepareStatement(ddlUpSql);
  psta.setString(1, name);
  psta.setString(2, Address);
  psta.setInt(3, age);
  psta.setString(4, sex);
  psta.setInt(5, id);
  psta.addBatch();
  psta.executeBatch();
  psta.close();
  conn.close();
 }


 public static void main(String[] args) throws SQLException {


   new UserSQLConn().dbCreate();
   UserInfo ufa = new UserInfo(1,"yanther","you kown",30,"M");
   UserInfo ufb = new UserInfo(2,"yang","123678",3,"M");
   UserInfo ufc = new UserInfo(3,"xzg","I dont kown",23,"M");
   UserInfo ufd = new UserInfo(4,"naruto","muye",18,"M");
   ArrayList<UserInfo> arr=new ArrayList<UserInfo>();


  arr.add(ufa);
  arr.add(ufb);
  arr.add(ufc);
  arr.add(ufd);
  new UserSQLConn().addUser(arr);
  new UserSQLConn().ddlDel(3);
  new UserSQLConn().ddlUpdate("name", "kof", 12, "M", 4);
  new UserSQLConn().ddlSelect();  
          
 }

}