java操作mysql数据库实例_MySQL

bitsCN.com

//接口类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();                    }               }          }     }}

bitsCN.com