JDBC连接MYSQL,JDBC增删改查 经典 范例

于 2011-09-13 15:31:00 发布
阅读量67 收藏 1
点赞数
文章标签： 数据库 java 面试

一切详情请看代码：绝对经典，注视写的很清楚

package cn.csdn.dao; import java.util.List; import cn.csdn.domain.User; public interface UserDao { boolean insert(User entity); boolean checkUser(String name); public boolean delete(User entity); public boolean update(User entity); User findById(Integer id); List findAll(); } 
package cn.csdn.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.csdn.domain.User;

public class UserDaoImpl implements UserDao{

	/*声明操作数据的对象*/
	private static Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	/*静态块*/
	static{
		/*准备驱动程序,加载驱动*/
		try {
			Class.forName("com.mysql.jdbc.Driver");
			/*面试必考题*/
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/job?user=root&password=admin&useUnicode=true&characterEncoding=UTF-8");	
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
	}
	
	@Override
	public boolean checkUser(String name) {
		/*第一步：声明返回变量*/
		boolean flag=false;
		/*第二步：获取连接对象 conn */
		/*第三步：定义sql语句 "select * from user where name='"+name+"'";*/
		String sql = "select * from user where name=?";
		
		try {
			/*第四步：根据sql语句创建预处理对象*/
			pstmt = conn.prepareStatement(sql);
			/*第五步：为站位符 赋值*/
			int index = 1;
			pstmt.setString(index++, name);
			/*第六步:执行查询*/
			rs = pstmt.executeQuery();
			/*第七步:判断*/
			if(rs.next()){
				flag = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/*第八步：关闭*/
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	@Override
	public boolean insert(User entity) {
		/*第一步：声明返回变量*/
		boolean flag=false;
		/*第二步：获取连接对象 conn */
		/*第三步：定义sql语句 ;insert  into user(name,sex,age)values('"+entity.getName()+"','"+entity.getSex()+"',"+entity.getAge()+");"*/
		String sql = "insert  into user(name,sex,age)values(?,?,?);";
		
		try {
			/*第四步：根据sql语句创建预处理对象*/
			pstmt = conn.prepareStatement(sql);
			/*第五步：为站位符 赋值*/
			int index = 1;
			pstmt.setObject(index++, entity.getName());
			pstmt.setObject(index++, entity.getSex());
			pstmt.setObject(index++, entity.getAge());
			/*第六步:执行更新*/
			int i = pstmt.executeUpdate();
			/*第七步:判断*/
			if(i>0){
				flag = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/*第八步：关闭*/
		
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		/*记得一定要把返回的值，修改成返回的变量*/
		return flag;
	}

	@Override
	public boolean delete(User entity) {
		/*第一步：声明返回变量*/
		boolean flag=false;
		/*第二步：获取连接对象 conn */
		/*第三步：定义sql语句 ;insert  into user(name,sex,age)values('"+entity.getName()+"','"+entity.getSex()+"',"+entity.getAge()+");"*/
		String sql = "delete from user where id=?";
		
		try {
			/*第四步：根据sql语句创建预处理对象*/
			pstmt = conn.prepareStatement(sql);
			/*第五步：为站位符 赋值*/
			int index = 1;
			pstmt.setInt(index++, entity.getId());
			/*第六步:执行更新*/
			int i = pstmt.executeUpdate();
			/*第七步:判断*/
			if(i>0){
				flag = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/*第八步：关闭*/
		
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		/*记得一定要把返回的值，修改成返回的变量*/
		return flag;
	}
	public static void main(String[] args) {
		/*看明白了嘛*/
		UserDao userDao = new UserDaoImpl();
		/*boolean flag = userDao.checkUser("redarmy");
		if(flag){
			System.out.println("用户名已经被占用了");
		}else{
			System.out.println("用户名还没有被使用,你可以使用此用户名");
		}*/
		
		/*boolean flag = userDao.insert(new User(null, "红军12", "男", 20));
		if(flag){
			System.out.println("注册成功");
		}else{
			System.out.println("注册失败");
		}*/
		
		/*List users = userDao.findAll();
		for (int i = 0; i < users.size(); i++) {
			User entity = (User) users.get(i);
			System.out.println(entity.getId()+"  "+entity.getName());
		}*/
		
		//userDao.update(new User(7,"孙悟空","女",10000));
		User entity = userDao.findById(7);
        System.out.println(entity.getId()+"  "+entity.getName());
		
		
	}

	@Override
	public List findAll() {
		/*第一步:定义返回结果*/
		List allentities = new ArrayList();
		/*第二步:获取连接对象*/
		/*第三步：定义sql语句*/
		String sql ="select * from user";
		try {
			/*第四步：根据sql语句创建预处理对象*/
			pstmt = conn.prepareStatement(sql);
			/*第五步：为站位符 赋值*/
		
			
			/*第六步:执行查询*/
			rs = pstmt.executeQuery();
			/*第七步:判断*/
			while(rs.next()){
				User entity = new User();
				entity.setId(rs.getInt("id"));
				entity.setName(rs.getString("name"));
				entity.setSex(rs.getString("sex"));
				entity.setAge(rs.getInt("age"));
				allentities.add(entity);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/*第八步：关闭*/
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return allentities;
	}
	
	
	
	@Override
	public boolean update(User entity) {
		/*第一步：声明返回变量*/
		boolean flag=false;
		/*第二步：获取连接对象 conn */
		/*第三步：定义sql语句 ;insert  into user(name,sex,age)values('"+entity.getName()+"','"+entity.getSex()+"',"+entity.getAge()+");"*/
		String sql = "update user set name=?,sex=?,age=? where id=?";
		
		try {
			/*第四步：根据sql语句创建预处理对象*/
			pstmt = conn.prepareStatement(sql);
			/*第五步：为站位符 赋值*/
			int index = 1;
			pstmt.setObject(index++, entity.getName());
			pstmt.setObject(index++, entity.getSex());
			pstmt.setObject(index++, entity.getAge());
			pstmt.setInt(index++, entity.getId());
			/*第六步:执行更新*/
			int i = pstmt.executeUpdate();
			/*第七步:判断*/
			if(i>0){
				flag = true;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/*第八步：关闭*/
		
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		/*记得一定要把返回的值，修改成返回的变量*/
		return flag;
	}

	@Override
	public User findById(Integer id) {
		/*第一步:定义返回结果*/
		User entity = new User();
		/*第二步:获取连接对象*/
		/*第三步：定义sql语句*/
		String sql ="select * from user where id=?";
		try {
			/*第四步：根据sql语句创建预处理对象*/
			pstmt = conn.prepareStatement(sql);
			/*第五步：为站位符 赋值*/
			int index = 1;
			pstmt.setInt(index++, id);
			/*第六步:执行查询*/
			rs = pstmt.executeQuery();
			/*第七步:判断*/
			if(rs.next()){
				
				entity.setId(rs.getInt("id"));
				entity.setName(rs.getString("name"));
				entity.setSex(rs.getString("sex"));
				entity.setAge(rs.getInt("age"));
				
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		/*第八步：关闭*/
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return entity;
	}

}
package cn.csdn.domain; import java.io.Serializable; /*业务Bean*/ public class User implements Serializable{ /** * */ private static final long serialVersionUID = 2646300928284173726L; /**递增序列*/ private Integer id; /**姓名*/ private String name; /*性别*/ private String sex; /*年龄*/ private Integer age; public User() { super(); // TODO Auto-generated constructor stub } public User(Integer id, String name, String sex, Integer age) { super(); this.id = id; this.name = name; this.sex = sex; this.age = age; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } } 

 

----------------代码源自于redarmychen
 

 

 


                

        




    

  



    

      

        

            

              
                
                  iteye_12869
                
              
            

            

                关注
              
            

        

        

          
      

      










            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

	
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值