java jdbc连接mysql数据库实现增删改查操作

点击打开链接http://www.jb51.net/article/88300.htm

package com.czgo.db;

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { private static final String URL = "jdbc:mysql://127.0.0.1:3306/imooc"; private static final String UNAME = "root"; private static final String PWD = "root"; private static Connection conn = null; static { try { // 1.加载驱动程序 Class.forName("com.mysql.jdbc.Driver"); // 2.获得数据库的连接 conn = DriverManager.getConnection(URL, UNAME, PWD); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } public static Connection getConnection() { return conn; } }

package com.czgo.model;
 
import java.io.Serializable;
 
/**
 * 实体类：女神类
 * 
 * @author AlanLee
 * 
 */
public class Goddess implements Serializable
{
  private static final long serialVersionUID = 1L;
 
  /**
   * 唯一主键
   */
  private Integer id;
  /**
   * 姓名
   */
  private String name;
  /**
   * 手机号码
   */
  private String mobie;
  /**
   * 电子邮件
   */
  private String email;
  /**
   * 家庭住址
   */
  private String address;
 
  public Integer getId()
  {
    return id;
  }
 
  public void setId(Integer id)
  {
    this.id = id;
  }
 
  public String getName()
  {
    return name;
  }
 
  public void setName(String name)
  {
    this.name = name;
  }
 
  public String getMobie()
  {
    return mobie;
  }
 
  public void setMobie(String mobie)
  {
    this.mobie = mobie;
  }
 
  public String getEmail()
  {
    return email;
  }
 
  public void setEmail(String email)
  {
    this.email = email;
  }
 
  public String getAddress()
  {
    return address;
  }
 
  public void setAddress(String address)
  {
    this.address = address;
  }
}

package com.czgo.dao;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
import com.czgo.db.DBUtil;
import com.czgo.model.Goddess;
 
/**
 * 数据层处理类
 * 
 * @author AlanLee
 * 
 */
public class GoddessDao
{
  /**
   * 查询全部女神
   * 
   * @return
   * @throws SQLException
   */
  public List<Goddess> query() throws SQLException
  {
    List<Goddess> goddessList = new ArrayList<Goddess>();
 
    // 获得数据库连接
    Connection conn = DBUtil.getConnection();
 
    StringBuilder sb = new StringBuilder();
    sb.append("select id,name,mobie,email,address from goddess");
 
    // 通过数据库的连接操作数据库，实现增删改查
    PreparedStatement ptmt = conn.prepareStatement(sb.toString());
 
    ResultSet rs = ptmt.executeQuery();
 
    Goddess goddess = null;
 
    while (rs.next())
    {
      goddess = new Goddess();
      goddess.setId(rs.getInt("id"));
      goddess.setName(rs.getString("name"));
      goddess.setMobie(rs.getString("mobie"));
      goddess.setEmail(rs.getString("email"));
      goddess.setAddress(rs.getString("address"));
 
      goddessList.add(goddess);
    }
    return goddessList;
  }
 
  /**
   * 查询单个女神
   * 
   * @return
   * @throws SQLException
   */
  public Goddess queryById(Integer id) throws SQLException
  {
    Goddess g = null;
 
    Connection conn = DBUtil.getConnection();
 
    String sql = "" + " select * from imooc_goddess " + " where id=? ";
 
    PreparedStatement ptmt = conn.prepareStatement(sql);
 
    ptmt.setInt(1, id);
 
    ResultSet rs = ptmt.executeQuery();
 
    while (rs.next())
    {
      g = new Goddess();
      g.setId(rs.getInt("id"));
      g.setName(rs.getString("name"));
      g.setMobie(rs.getString("mobie"));
      g.setEmail(rs.getString("email"));
      g.setAddress(rs.getString("address"));
    }
 
    return g;
  }
 
  /**
   * 添加女神
   * 
   * @throws SQLException
   */
  public void addGoddess(Goddess goddess) throws SQLException
  {
    // 获得数据库连接
    Connection conn = DBUtil.getConnection();
 
    String sql = "insert into goddess(name,mobie,email,address) values(?,?,?,?)";
 
    PreparedStatement ptmt = conn.prepareStatement(sql);
 
    ptmt.setString(1, goddess.getName());
    ptmt.setString(2, goddess.getMobie());
    ptmt.setString(3, goddess.getEmail());
    ptmt.setString(4, goddess.getAddress());
 
    ptmt.execute();
  }
 
  /**
   * 修改女神资料
   * 
   * @throws SQLException
   */
  public void updateGoddess(Goddess goddess) throws SQLException
  {
    Connection conn = DBUtil.getConnection();
 
    String sql = "update goddess set name=?,mobie=?,email=?,address=? where id=?";
 
    PreparedStatement ptmt = conn.prepareStatement(sql);
 
    ptmt.setString(1, goddess.getName());
    ptmt.setString(2, goddess.getMobie());
    ptmt.setString(3, goddess.getEmail());
    ptmt.setString(4, goddess.getAddress());
 
    ptmt.execute();
  }
 
  /**
   * 删除女神
   * 
   * @throws SQLException
   */
  public void deleteGoddess(Integer id) throws SQLException
  {
    Connection conn = DBUtil.getConnection();
 
    String sql = "delete from goddess where id=?";
 
    PreparedStatement ptmt = conn.prepareStatement(sql);
 
    ptmt.setInt(1, id);
 
    ptmt.execute();
  }
}

package com.czgo.action;
 
import java.sql.SQLException;
import java.util.List;
 
import com.czgo.dao.GoddessDao;
import com.czgo.model.Goddess;
 
/**
 * 控制层，直接在这里构建数据，界面的数据则通过请求传递接收即可，亦是同理
 * 
 * @author AlanLee
 * 
 */
public class GoddessAction
{
  /**
   * 新增女神
   * 
   * @param goddess
   * @throws Exception
   */
  public void add(Goddess goddess) throws Exception
  {
    GoddessDao dao = new GoddessDao();
    goddess.setName("苍井空");
    goddess.setMobie("52220000");
    goddess.setEmail("52220000@qq.com");
    goddess.setAddress("北京红灯区");
    dao.addGoddess(goddess);
  }
 
  /**
   * 查询单个女神
   * 
   * @param id
   * @return
   * @throws SQLException
   */
  public Goddess get(Integer id) throws SQLException
  {
    GoddessDao dao = new GoddessDao();
    return dao.queryById(id);
  }
 
  /**
   * 修改女神
   * 
   * @param goddess
   * @throws Exception
   */
  public void edit(Goddess goddess) throws Exception
  {
    GoddessDao dao = new GoddessDao();
    dao.updateGoddess(goddess);
  }
 
  /**
   * 删除女神
   * 
   * @param id
   * @throws SQLException
   */
  public void del(Integer id) throws SQLException
  {
    GoddessDao dao = new GoddessDao();
    dao.deleteGoddess(id);
  }
 
  /**
   * 查询全部女神
   * 
   * @return
   * @throws Exception
   */
  public List<Goddess> query() throws Exception
  {
    GoddessDao dao = new GoddessDao();
    return dao.query();
  }
 
  /**
   * 测试是否成功
   * 
   * @param args
   * @throws SQLException
   */
  public static void main(String[] args) throws SQLException
  {
    GoddessDao goddessDao = new GoddessDao();
 
    List<Goddess> goddessList = goddessDao.query();
 
    for (Goddess goddess : goddessList)
    {
      System.out.println(goddess.getName() + "," + goddess.getMobie() + "," + goddess.getEmail());
    }
  }
}

这样，一个简单的java jdbc 连接mysql数据库 实现增删改查便完成了，大家可以在查询的基础上试着去做一个高级查询，也就是多条件查询来巩固jdbc的使用。