JAVA实现对SQLServer增，删，改，查！
刚学到数据库！自己写的代码，实现了增，删，改，查。基本功能！贴出来分享下！！不足之处，见谅。望指点。
userInfo.java
package com.sql.form;
public class UserInfo {
int userId;
String userName;
String userAddress;
int userAge;
String userSex;
public UserInfo(int id,String name,String addre,int age,String sex){
this.userAddress=addre;
this.userAge=age;
this.userId=id;
this.userName=name;
this.userSex=sex;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
public int getUserAge() {
return userAge;
}
public void setUserAge(int userAge) {
this.userAge = userAge;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public String toString() {
return "address:" + userAddress + "\t" + "age:" + userAge + "\t"
+ "name:" + userName + "\t" + "sex:" + userSex + "\t" + "id:"
+ userId;
}
}
Java SQL?Server 数据库
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();
}
}
