java连接mysql

1、首先创建一个连接类ConnDB.java

package com.cust;

 

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class ConnDB {

 public Connection conn = null;
 public Statement st = null;
 public ResultSet rs = null;

 public Connection getConnection() {
  try {
   Class.forName("com.mysql.jdbc.Driver");
   String url = "jdbc:mysql://127.0.0.1:3306/cust?user=root&password=123456&useUnicode=true&characterEncoding=UTF-8";
   conn = DriverManager.getConnection(url);
   return conn;
  } catch (Exception e) {
   System.out.print("数据库连接失败了");
   e.printStackTrace();
  }
  return null;
 }

 // 关闭连接
 public void closeConnection() {
  if (rs != null) {
   try {
    rs.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if (st != null) {
   try {
    st.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
  if (conn != null) {
   try {
    conn.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

}
2、创建一个接口类IDao.java

 

package com.cust;

import java.util.ArrayList;

public interface IDao
{
 public int insert(CustBean bea);
 public int delete(String id);
 public int update(CustBean bea);
 public ArrayList selectAll();
 public CustBean selectCust(CustBean cBean);
}

 

3、实现这个接口类CustDAO

 

package com.cust;

import java.sql.*;
import java.text.DateFormat;
import java.util.*;
import java.util.Date;

public class CustDAO extends ConnDB implements IDao {
 PreparedStatement pstmt;

 // 实现添加
 public int insert(CustBean bea) {
  String sql = "insert into cust values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  super.getConnection();  
  try {
   Date now = new Date();
   DateFormat d = DateFormat.getDateInstance();
   String cDate = d.format(now);

   pstmt = conn.prepareStatement(sql);
   Calendar CD = Calendar.getInstance();
   String id="";
   int YY = CD.get(Calendar.YEAR) ;
     int MM = CD.get(Calendar.MONTH)+1;
     id+=MM;
     int DD = CD.get(Calendar.DATE);
     id+=DD;
     int HH = CD.get(Calendar.HOUR);
     id+=HH;
     int NN = CD.get(Calendar.MINUTE);
     id+=NN;
     int SS = CD.get(Calendar.SECOND);
     id+=SS;
     int MI = CD.get(Calendar.MILLISECOND);
     id+=MI;
   pstmt.setString(1, id);
   pstmt.setString(2, bea.getName());
   pstmt.setString(3, bea.getSource());
   pstmt.setString(4, bea.getCustServer());
   pstmt.setString(5, bea.getCustType());
   pstmt.setString(6, bea.getProvince());
   pstmt.setString(7, bea.getCity());
   pstmt.setString(8, bea.getRegion());
   pstmt.setString(9, bea.getAddress());
   pstmt.setString(10, bea.getTel());
   pstmt.setString(11, bea.getPhone());
   pstmt.setString(12, bea.getCarType());
   pstmt.setString(13, bea.getAlipay());
   pstmt.setString(14, "0");
   pstmt.setString(15, cDate);
   pstmt.setString(16, cDate);
   pstmt.setString(17, bea.getRemark());

   int x = pstmt.executeUpdate();
   return x;
  } catch (SQLException e) {
   System.out.println("新成员添加失败！！");
   e.printStackTrace();
  } finally {
   super.closeConnection();
  }
  return 0;
 }

 // 实现删除
 public int delete(String id) {
  String sql = "delete from cust where id=?";

  try {
   pstmt = super.getConnection().prepareStatement(sql);
   pstmt.setString(1, id);
   int x = pstmt.executeUpdate();
   return x;
  } catch (SQLException e) {
   System.out.print("删除方法错误！！！");
   e.printStackTrace();
  } finally {
   super.closeConnection();
  }
  return 0;
 }

 // 实现查询所有
 public ArrayList selectAll() {
  String sql = "select * from cust";
  ArrayList list = new ArrayList();

  try {
   pstmt = super.getConnection().prepareStatement(sql);
   rs = pstmt.executeQuery();
   while (rs.next()) {
    CustBean cBean = new CustBean();
    cBean.setId(rs.getString(1));
    cBean.setName(rs.getString(2));
    cBean.setSource(rs.getString(3));
    cBean.setCustServer(rs.getString(4));
    cBean.setCustType(rs.getString(5));
    cBean.setProvince(rs.getString(6));
    cBean.setCity(rs.getString(7));
    cBean.setRegion(rs.getString(8));
    cBean.setAddress(rs.getString(9));
    cBean.setTel(rs.getString(10));
    cBean.setPhone(rs.getString(11));
    cBean.setCarType(rs.getString(12));
    cBean.setAlipay(rs.getString(13));
    cBean.setTotal(rs.getString(14));
    cBean.setCreateDate(rs.getString(15));
    cBean.setLastCreateDate(rs.getString(16));
    cBean.setRemark(rs.getString(17));
    list.add(cBean);
   }
  } catch (SQLException e) {
   e.printStackTrace();
  } finally {
   super.closeConnection();
  }
  return list;
 }

 // 实现单个查询
 public CustBean selectCust(CustBean cBean) {
  CustBean custBean = new CustBean();
  try {
   if(cBean.getId()==null)
   {
    String sql = "select * from cust where name=?";
    pstmt = super.getConnection().prepareStatement(sql);
    pstmt.setString(1, cBean.getName());

   }else{
    String sql = "select * from cust where id=?";
    pstmt = super.getConnection().prepareStatement(sql);
    pstmt.setString(1, cBean.getId());
   }
   rs = pstmt.executeQuery();
   while(rs.next())
      {
    custBean.setId(rs.getString(1));
    custBean.setName(rs.getString(2));
    custBean.setSource(rs.getString(3));
    custBean.setCustServer(rs.getString(4));
    custBean.setCustType(rs.getString(5));
    custBean.setProvince(rs.getString(6));
    custBean.setCity(rs.getString(7));
    custBean.setRegion(rs.getString(8));
    custBean.setAddress(rs.getString(9));
    custBean.setTel(rs.getString(10));
    custBean.setPhone(rs.getString(1));
    custBean.setCarType(rs.getString(12));
    custBean.setAlipay(rs.getString(13));
    custBean.setTotal(rs.getString(14));
    custBean.setCreateDate(rs.getString(15));
    custBean.setLastCreateDate(rs.getString(16));
    custBean.setRemark(rs.getString(17));
      }
   return custBean;
  } catch (SQLException e) {
   System.out.print("查询单个记录错误！");
   e.printStackTrace();
  } finally {
   super.closeConnection();
  }
  return null;
 }

 // 实现修改
 public int update(CustBean bea) {

  String sql = "update cust set Name=?,Source=?,CustServer=?,CustType=?,Province=?,City=?,Region=?,Address=?,Tel=?,Phone=?,CarType=?,Alipay=?,LastCreateDate=?,Remark=? where id=?";

  try {
   PreparedStatement pstmt = super.getConnection().prepareStatement(sql);
   
   pstmt.setString(1, bea.getName());
   pstmt.setString(2, bea.getSource());
   pstmt.setString(3, bea.getCustServer());
   pstmt.setString(4, bea.getCustType());
   pstmt.setString(5, bea.getProvince());
   pstmt.setString(6, bea.getCity());
   pstmt.setString(7, bea.getRegion());
   pstmt.setString(8, bea.getAddress());
   pstmt.setString(9, bea.getTel());
   pstmt.setString(10, bea.getPhone());
   pstmt.setString(11, bea.getCarType());
   pstmt.setString(12, bea.getAlipay());
   pstmt.setString(13, bea.getLastCreateDate());
   pstmt.setString(14, bea.getRemark());
   pstmt.setString(15, bea.getId());
   int x = pstmt.executeUpdate();
         return x;
  } catch (SQLException e) {
   System.out.print("修改方法错误！！！");
   e.printStackTrace();
  }finally{
   super.closeConnection();
  }
  return 0;
 }

}