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

}

}

}

}

}