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、删除表