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