Java 数据库编程之mysql

                                     Java 数据库编程之mysql

一、基本知识

1、  JDBC 提供了与平台无关的执行sql的API

2、  JDBC操作步骤

a．加载数据库驱动程序，将开发包的地址加载classpath中

b．建立连接，视同加上用户名和密码

c．使用语句进行数据库操作。

d．关闭数据库

二、mysql中建立连接和关闭连接的功能类  （背景：李兴华 人员管理系统）

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

public class DataBaseConnection {
//定义mysql的数据库驱动程序
	private static final String DBDRIVER = "org.gjt.mm.mysql.Driver" ;
//定义链接地址
	private static final String DBURL = "jdbc:mysql://localhost:3306/test3" ;
//定义链接用户名
	private static final String DBUSER = "root" ;
//定义密码
	private static final String DBPASS = "123456" ;
	private Connection conn = null ;
	public DataBaseConnection(){
		try {
			Class.forName(DBDRIVER) ;//加载驱动程序
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
//链接mysql数据库时要加上链接的用户名和密码
			conn = DriverManager.getConnection(DBURL, DBUSER,DBPASS) ;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public Connection getConnection(){
		return this.conn ;
	}
	public void close(){
		if(this.conn!=null){
			try {
				this.conn.close() ;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

三、mysql增删改查功能类使用的代理模式

package org.lxh.useradmin.dao.proxy;
import java.util.List;
import org.lxh.useradmin.dao.IUserDAO;
import org.lxh.useradmin.dao.impl.IUserDAOImpl;
import org.lxh.useradmin.dbc.DataBaseConnection;
import org.lxh.useradmin.vo.User;

public class IUserDAOProxy implements IUserDAO {
	private DataBaseConnection dbc = null;
	private IUserDAO dao = null;

	public IUserDAOProxy() {
		this.dbc = new DataBaseConnection();
		this.dao = new IUserDAOImpl(this.dbc.getConnection());
	}

	@Override
	public boolean doCreate(User user) throws Exception {
		boolean flag = true;
		try {
			flag = this.dao.doCreate(user);
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return flag;
	}

	@Override
	public boolean doDelete(int id) throws Exception {
		boolean flag = true;
		try {
			flag = this.dao.doDelete(id);
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return flag;
	}

	@Override
	public boolean doUpdate(User user) throws Exception {
		boolean flag = true;
		try {
			flag = this.dao.doUpdate(user);
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return flag;
	}

	@Override
	public List<User> findAll(String keyWord) throws Exception {
		List<User> all = null;
		try {
			all = this.dao.findAll(keyWord);
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return all;
	}

	@Override
	public User findById(int id) throws Exception {
		User user = null;
		try {
			user = this.dao.findById(id);
		} catch (Exception e) {
			throw e;
		} finally {
			this.dbc.close();
		}
		return user;
	}
}

package org.lxh.useradmin.dao.impl;

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

import org.lxh.useradmin.dao.IUserDAO;
import org.lxh.useradmin.vo.User;

public class IUserDAOImpl implements IUserDAO {
	private Connection conn = null;

	public IUserDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doCreate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO user(name,sex,birthday) VALUES (?,?,?) ";
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, user.getName()); // 所有的内容从user类中取出
			pstmt.setString(2, user.getSex()); // 所有的内容从user类中取出
			pstmt.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
			if (pstmt.executeUpdate() > 0) {// 至少已经更新了一行
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally { // 不管如何抛出，最终肯定是要进行数据库的关闭操作的
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e1) {

				}
			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int id) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM user WHERE id=? ";
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, id); // 所有的内容从user类中取出
			if (pstmt.executeUpdate() > 0) {// 至少已经更新了一行
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally { // 不管如何抛出，最终肯定是要进行数据库的关闭操作的
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e1) {

				}
			}
		}
		return flag;
	}

	@Override
	public boolean doUpdate(User user) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE user SET name=?,sex=?,birthday=? WHERE id=?";
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, user.getName()); // 所有的内容从user类中取出
			pstmt.setString(2, user.getSex()); // 所有的内容从user类中取出
			pstmt.setDate(3, new java.sql.Date(user.getBirthday().getTime()));
			pstmt.setInt(4, user.getId());
			if (pstmt.executeUpdate() > 0) {// 至少已经更新了一行
				flag = true;
			}
		} catch (Exception e) {
			throw e;
		} finally { // 不管如何抛出，最终肯定是要进行数据库的关闭操作的
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e1) {

				}
			}
		}
		return flag;
	}

	@Override
	public List<User> findAll(String keyWord) throws Exception {
		List<User> all = new ArrayList<User>();
		PreparedStatement pstmt = null;
		String sql = "SELECT id,name,sex,birthday FROM user WHERE name LIKE ? OR sex LIKE ? OR birthday LIKE ?";
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyWord + "%");
			pstmt.setString(2, "%" + keyWord + "%");
			pstmt.setString(3, "%" + keyWord + "%");
			ResultSet rs = pstmt.executeQuery(); // 执行查询操作
			while (rs.next()) {
				User user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setSex(rs.getString(3));
				user.setBirthday(rs.getDate(4));
				all.add(user); // 所有的内容向集合中插入
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally { // 不管如何抛出，最终肯定是要进行数据库的关闭操作的
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e1) {

				}
			}
		}
		return all;
	}

	@Override
	public User findById(int id) throws Exception {
		User user = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT id,name,sex,birthday FROM user WHERE id=?";
		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery(); // 执行查询操作
			if (rs.next()) {
				user = new User();
				user.setId(rs.getInt(1));
				user.setName(rs.getString(2));
				user.setSex(rs.getString(3));
				user.setBirthday(rs.getDate(4));
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally { // 不管如何抛出，最终肯定是要进行数据库的关闭操作的
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (Exception e1) {
				}
			}
		}
		return user;
	}
}

四、Mysql使用手册

1、  create/ drop database 数据库名称；

2、  use 数据库名；

3、  创建数据库表

4、删除表