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

mtkof 2013-06-15 12:52:08

刚学到数据库！自己写的代码，实现了增，删，改，查。基本功能！贴出来分享下！！不足之处，见谅。望指点。
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;



	}

      

}

UserSQLConn.java



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

		

		

		

          

	}



}