java操作数据库sqlserver（三）

第三步：实现接口方法的实现类

package com.qilu.jdbcDemo.dao.IuserImp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;
import java.sql.*;

import javax.swing.JOptionPane;

import com.qilu.jdbcDemo.dao.IUserDao;
import com.qilu.jdbcDemo.entity.User;


/*
 *这是一个实体类，实现了IUserDao接口中的所有方法 
 */
public class UserDaoImp implements IUserDao {

	//插入一条数据 --实现方法
	@Override
	public int insertOneUser(User user) {
		
		int i = 0;
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			Statement stmt = conn.createStatement();
			//插入语句
			i = stmt.executeUpdate("insert into TestStudent values ("+user.getId()+",'"+user.getName()+"','"+user.getSex()+"',"+user.getAge()+")");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
		
	}

	//删除一条数据
	@Override
	public int deleteOneUser(User user) {
		
		int i = 0;
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			Statement stmt = conn.createStatement();
			//插入语句
			i = stmt.executeUpdate("delete from TestStudent where id = " + user.getId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;

	}

	//更新一条数据
	@Override
	public int updateOneUser(User user) {
		
		int i = 0;
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			//更新语句
			String sql = "update TestStudent set name = ?,sex = ?,age = ?  where id = ?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			//设置参数
			stmt.setString(1, user.getName());
			stmt.setString(2, user.getSex());
			stmt.setInt(3,user.getAge());
			stmt.setInt(4, user.getId());
			i = stmt.executeUpdate();
 		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}

	//通过userId查找一条数据
	@Override
	public User findOneUser(int userId) {
	
		User user = new User();
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			Statement stmt = conn.createStatement();
			//得到查询的结果
			ResultSet rs = stmt.executeQuery("select * from TestStudent where id = "+userId );
			
			while(rs.next()){

				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setSex(rs.getString("sex"));
				user.setAge(rs.getInt("age"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return user;
	}

	//通过userName查找一条数据
	@Override
	public User frindOneUser(String userName) {

		User user = new User();
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			Statement stmt = conn.createStatement();
			//得到查询的结果
			ResultSet rs = stmt.executeQuery("select * from TestStudent where name = '"+userName+"'" );
			
			while(rs.next()){

				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setSex(rs.getString("sex"));
				user.setAge(rs.getInt("age"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		return user;
	}

	//查找出所有的数据
	@Override
	public List<User> findAllUser() {
		
		//建立一个集合，放所有数据
		List<User> userList = new ArrayList<User>();
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			Statement stmt = conn.createStatement();
			//得到查询的结果
			ResultSet rs = stmt.executeQuery("select * from TestStudent");
			
			while(rs.next()){
				//每循环一次产生一个user对象
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setName(rs.getString("name"));
				user.setSex(rs.getString("sex"));
				user.setAge(rs.getInt("age"));
				//把user放到userList集合中去
				userList.add(user);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return userList;
	}

	//验证数据是否存在
	@Override
	public boolean validate(User user) {
		
		boolean b = false;
		
		//连接数据库
		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;databaseName=JDBCDemo","sa","100862");
			Statement stmt = conn.createStatement();
			//得到查询的结果
			ResultSet rs = stmt.executeQuery("select * from TestStudent where id = '"+user.getId()+"'  and name = '"+user.getName()+"'" );
			
			if(rs.next()){
				
				b = true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}		
		
		return b;
	}

}