java操作mysql数据库实例

//接口类
package com.syxy.dao;


import com.syxy.domain.User;


//操纵数据库的 dao接口
public interface UserDao {
       //加入一个用户
       public void addUser(User user);
       //得到一个用户
       public User getUser( int userId);
       //寻找一个用户
       public User findUser(String loginName);
       //更新用户数据
       public void update(User user);
       //删除一个用户
       public void delete(User user);
}


//接口的实现类
package com.syxy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.syxy.dao.UserDao;
import com.syxy.domain.User;
import com.syxy.utils.jdbcUtils;

public class UserDaoJdbcImpl implements UserDao {

     @Override
     public void addUser(User user) {
          // TODO Auto-generated method stub
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "insert into user(name, birthday, monery) values(?,?,?)";
               ps = conn.prepareStatement(sql);
               ps.setString(1, user.getName());
               ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
               ps.setFloat(3, user.getMoney());
               ps.executeUpdate();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
     }

     @Override
     public User getUser(int userId) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          User user = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "select id，name,money,birthday from user where id=?";
               ps = conn.prepareStatement(sql);
               ps.setInt(1, userId);
               rs = ps.executeQuery();
               while(rs.next()){
                    user = MappingUser(rs);
               }
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
          return user;
     }

     @Override
     public User findUser(String loginName) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          User user = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "select id,money,birthday from user where name=?";
               ps = conn.prepareStatement(sql);
               ps.setString(1, loginName);
               rs = ps.executeQuery();
               while(rs.next()){
                    user = MappingUser(rs);
               }
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
          return user;
     }

     private User MappingUser(ResultSet rs) throws SQLException {
          User user = new User();
          user.setId(rs.getInt("id"));
          user.setName(rs.getString("name"));
          user.setMoney(rs.getFloat("money"));
          user.setBirthday(rs.getDate("birthday"));
          return user;
     }

     @Override
     public void update(User user) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "update user set name=?, birthday=?, money=? where id=?";
               ps = conn.prepareStatement(sql);
               ps.setString(1, user.getName());
               ps.setDate(2, new java.sql.Date(user.getBirthday().getTime()));
               ps.setFloat(3, user.getMoney());
               ps.setInt(4, user.getId());
               ps.executeUpdate();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
     }

     @Override
     public void delete(User user) {
          Connection conn = null;
          PreparedStatement ps = null;
          ResultSet rs = null;
          try {
               conn = jdbcUtils.getConnection();
               String sql = "delete from user where id=?";
               ps = conn.prepareStatement(sql);
               ps.setInt(1, user.getId());
               ps.executeUpdate();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               jdbcUtils.free(rs, ps, conn);
          }
     }
}



//domain用户类
package com.syxy.domain;


import java.util.Date;


public class User {
       private int id ;
       private String name ;
       private Date birthday ;
       private float money ;
      
       public int getId() {
             return id ;
      }
       public void setId( int id) {
             this .id = id;
      }
       public String getName() {
             return name ;
      }
       public void setName(String name) {
             this .name = name;
      }
       public Date getBirthday() {
             return birthday ;
      }
       public void setBirthday(Date birthday) {
             this .birthday = birthday;
      }
       public float getMoney() {
             return money ;
      }
       public void setMoney( float money) {
             this .money = money;
      }  
}


//dao工厂类（方便复用）
package com.syxy.dao;


import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.Properties;


public class DaoFactory {
       private static UserDao userDao = null ;
       private static DaoFactory instance = new DaoFactory();
      
       private DaoFactory(){
             try {
                  Properties prop = new Properties();
                  InputStream inStream = new FileInputStream(
                               new File("src/daoconfig.properties" ));
                  prop.load(inStream);
                  String userDaoClass = prop.getProperty("userDaoClass" );
                   userDao = (UserDao) Class.forName(userDaoClass).newInstance();
            } catch (Exception e) {
                   // TODO: handle exception
            }
      }
      
       public static DaoFactory getInstance(){
             return instance ;
      }
      
       public UserDao getUserDao(){
             return userDao ;
      }
}


//操作数据库的工具类
package com.syxy.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class jdbcUtils {
     private static String url = "jdbc：mysql://localhost:3306/jdbc";
     private static String user = "root";
     private static String password = "lxtalx";
     
     private  jdbcUtils(){}
     static{
          try {
               Class.forName("com.mysql.jdbc.Driver");
          } catch (ClassNotFoundException e) {
               throw new ExceptionInInitializerError(e);
          }
     }
     
     public static Connection getConnection() throws SQLException{
          return DriverManager.getConnection(url, user, password);
     }
     
     public static void free(ResultSet rs, Statement st, Connection conn){
          try {
               if(rs!=null) 
                    rs.close();
          } catch (Exception e) {
               e.printStackTrace();
          }finally{
               try {
                    if(st!=null)
                         st.close();
               } catch (Exception e2) {
                    e2.printStackTrace();
               }finally{
                    try {
                         conn.close();
                    } catch (Exception e3) {
                         e3.printStackTrace();
                    }
               }
          }
     }
}