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 ls) throws SQLException{

Connection conn=getConn(driverName, dbUrl, us, pw);

String insertSql="insert into UserInfo values(?,?,?,?,?);";

PreparedStatement psta=conn.prepareStatement(insertSql);

Iterator 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 arr=new ArrayList();

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();

}

}