java操作mysql数据库实例【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(); } } } }}