JDBC For Mysql

JDBC MySQL

(1)    连接MYSQL数据库

  public static Connection getConnection() {
        Connection conn = null;
        try {
            String driver = "com.mysql.jdbc.Driver"; // 数据库驱动
            String url = "jdbc:MySQL://127.0.0.1:3306/mysqltest1?useUnicode=true&characterEncoding=utf-8";// 数据库
            String user = "root"; // 用户名
            String password = ""; // 密码
            Class.forName(driver); // 加载数据库驱动
            if (null == conn) {
                conn = DriverManager.getConnection(url, user, password);
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can't find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

程序调用

try
        {
            Connection conn =DBHelper.getConnection();
             if(conn != null && !conn.isClosed()) {        
                 System.out.println("资料库连线测试成功！");                 
                 conn.close();            
                 }             
        }
        catch (SQLException e)
        {
            System.out.println("资料库连线测试失败！");      
        }

(2)    新增数据

A 不带参数的

/**
     * 增删改【Add、Del、Update】
     *
     * @param sql
     * @return int
     */
    public static int executeNonQuery(String sql) {
        int result = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException err) {
            err.printStackTrace();
            free(null, stmt, conn);
        } finally {
            free(null, stmt, conn);
        }
        return result;
}
    String sql1="insert into bGoods(Goods_Name,Goods_Price) values ('微星笔记本',2800);";        
        System.out.println(DBHelper.executeNonQuery(sql1));
        System.out.println("数据插入完成！");      

B 带参数的

public static int executeNonQuery(String sql, Object... obj) {
        int result = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                pstmt.setObject(i + 1, obj[i]);
            }
            result = pstmt.executeUpdate();

        } catch (SQLException err) {
            err.printStackTrace();
            free(null, pstmt, conn);
        } finally {
            free(null, pstmt, conn);
        }
        return result;
}
//主程序调用
  String sql2="Insert bGoods(Goods_Name,Goods_Price) Values (?,?)";          
            Object[] obj = new Object[]{"华硕笔记本", 1200};
            System.out.println(DBHelper.executeNonQuery(sql2,obj));            
        System.out.println("数据插入完成！");   

(3)    修改数据

A 不带参数

  String sql1="update bOrder set Order_Money=160 where Order_ID=2";
            System.out.println(DBHelper.executeNonQuery(sql1));

B 带参数

   String sql2="update bOrder set Order_Money=? where Order_ID=?";
            Object[] obj = new Object[] {100,2};
            System.out.println(DBHelper.executeNonQuery(sql2,obj));   

(4)    删除数据

A 不带参数

String sql1="delete from bgoods where goods_ID=3";
System.out.println(DBHelper.executeNonQuery(sql1));

B 带参数

     String sql2="Delete From bgoods Where goods_id=?";
            Object[] obj = new Object[]{2};
            System.out.println(DBHelper.executeNonQuery(sql2,obj));

(5)    查询记录

A 不带参数

public static ResultSet executeQuery(String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs, stmt, conn);
        }
        return rs;
    }
--主程序
String sql1="Select * From bclient";
        //第一步：查询
        ResultSet rs1 = DBHelper.executeQuery(sql1);
        //第二步：输出
        try {
            while(rs1.next()){
                System.out.println("姓名："+rs1.getString(“user_name”));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //第三步：关闭
        DBHelper.free(rs1);

B 带参数

public static ResultSet executeQuery(String sql, Object... obj) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                pstmt.setObject(i + 1, obj[i]);
            }
            rs = pstmt.executeQuery();
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs, pstmt, conn);
        }
        return rs;
    }
//主程序
String sql2="Select * From bclient Where user_name=?";
        Object[] obj = new Object[]{"李四"};
        //第一步：查询
        ResultSet rs2 = DBHelper.executeQuery(sql2,obj);
        //第二步：输出
        try {
            while(rs2.next()){
                System.out.println("学号："+rs2.getString("user_name"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        //第三步：关闭
        DBHelper.free(rs2);

(6)    查询行数

/**
     * 获取查询记录的总行数
     *
     * @param sql
     * @return int
     */
    public static int getCount(String sql) {
        int result = 0;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql);
            rs.last();
            result = rs.getRow();
        } catch (SQLException err) {
            free(rs);
            err.printStackTrace();
        } finally {
            free(rs);
        }
        return result;
    }

    /**
     * 获取查询记录的总行数
     *
     * @param sql
     * @param obj
     * @return int
     */
    public static int getCount(String sql, Object... obj) {
        int result = 0;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, obj);
            rs.last();
            result = rs.getRow();
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            free(rs);
        }
        return result;
    } 
//主程序
//不带参数
   String sql1="Select * From bgoods";
        System.out.println(DBHelper.getCount(sql1));
//带参数
     String sql2="Select * From bgoods Where goods_name=?";
        Object[] obj = new Object[]{"联想笔记本"};
        System.out.println(DBHelper.getCount(sql2,obj));

(6)判断记录是否存在

/**
     * 判断记录是否存在
     *
     * @param sql
     * @return Boolean
     */
    public static Boolean isExist(String sql) {
        ResultSet rs = null;
        try {
            rs = executeQuery(sql);
            rs.last();
            int count = rs.getRow();
            if (count > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs);
            return false;
        } finally {
            free(rs);
        }
    } 

    /**
     * 判断记录是否存在
     *
     * @param sql
     * @return Boolean
     */
    public static Boolean isExist(String sql, Object... obj) {
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, obj);
            rs.last();
            int count = rs.getRow();
            if (count > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs);
            return false;
        } finally {
            free(rs);
        }
    }

主程序

//不传递参数
String sql1="Select * From bgoods";
System.out.println(DBHelper.isExist(sql1));
//传递参数
       String sql2="Select * From bgoods Where goods_name=?";
            Object[] obj = new Object[]{"华硕笔记本1"};
            System.out.println(DBHelper.isExist(sql2,obj));

(7) 执行存储过程

System.out.println(DBHelper.executeNonQuery("CALL update_Fun ('1','王五AA123')"));
存储过程
/*
CREATE procedure update_Fun
(
 p_User_ID varchar(50),
 P_UserName varchar(50)
)
begin
START TRANSACTION;
update bClient set User_Name=P_UserName where User_ID=p_User_ID ;
update bOrder set Order_Money=Order_Money*0.9 where User_ID=p_User_ID ;
COMMIT;
select 用户编号,用户姓名,sum(订单金额)  as 订单总金额
from 用户订单
where  用户姓名=P_UserName
group by 用户编号,用户姓名;
end
*/     

(8) 事务操作

import java.sql.*;
public class OPMysqlDB {
    public static void main(String[] args) throws SQLException {
        // TODO Auto-generated method stub
        //1 连接Mysql数据库
        Connection conn1=null;//定义Connection
        String driver="com.mysql.jdbc.Driver";
        String url="jdbc:MySQL://127.0.0.1:3306/shopping?useUnicode=true&characterEncoding=utf-8";// ";
          String user = "root"; // 用户名
          String password = ""; // 密码
         try {
            Class.forName(driver);//加载Mysql数据库驱动
            if (conn1==null)
            {
                try {
                    conn1= DriverManager.getConnection(url, user, password);//连接Mysql数据库
                    System.out.println("Mysql数据库连接完成");
                    //事务
                    conn1.setAutoCommit(false);
                    Statement stmt = conn1.createStatement();
                    
                    String sql1="insert into bgoods(Goods_ID,Goods_Name,Goods_Price) values (120,'产品测试1',111.11);";
                    
                    String sql2="insert into bgoods(Goods_ID,Goods_Name,Goods_Price) values (120,'产品测试2',122.11);";
                    
                    String sql3="update bgoods set Goods_Price=1500.02 where Goods_ID=120;";
                    stmt.execute(sql1);                     
                    stmt.execute(sql2); 
                    stmt.execute(sql3);
                    conn1.commit();    
                    conn1.close();//关闭数据库                    
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    conn1.rollback();
                    System.out.println("Mysql执行回滚");
                    e.printStackTrace();
                }
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

 Mysql操作类如下：

package MysqlDbHelp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class DBHelper {

 

    // 此方法为获取数据库连接

    public static Connection getConnection() {
        Connection conn = null;
        try {
            String driver = "com.mysql.jdbc.Driver"; // 数据库驱动
            String url = "jdbc:MySQL://127.0.0.1:3306/mysqltest1?useUnicode=true&characterEncoding=utf-8";// 数据库
            String user = "root"; // 用户名
            String password = ""; // 密码
            Class.forName(driver); // 加载数据库驱动
            if (null == conn) {
                conn = DriverManager.getConnection(url, user, password);
            }
        } catch (ClassNotFoundException e) {
            System.out.println("Sorry,can't find the Driver!");
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

 

    /**
     * 增删改【Add、Del、Update】
     *
     * @param sql
     * @return int
     */
    public static int executeNonQuery(String sql) {
        int result = 0;
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            result = stmt.executeUpdate(sql);
        } catch (SQLException err) {
            err.printStackTrace();
            free(null, stmt, conn);
        } finally {
            free(null, stmt, conn);
        }
        return result;
    }

    /**
     * 增删改【Add、Delete、Update】
     *
     * @param sql
     * @param obj
     * @return int
     */
    public static int executeNonQuery(String sql, Object... obj) {
        int result = 0;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                pstmt.setObject(i + 1, obj[i]);
            }
            result = pstmt.executeUpdate();

        } catch (SQLException err) {
            err.printStackTrace();
            free(null, pstmt, conn);
        } finally {
            free(null, pstmt, conn);
        }
        return result;
    }

 

    /**
     * 查【Query】
     *
     * @param sql
     * @return ResultSet
     */
    public static ResultSet executeQuery(String sql) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs, stmt, conn);
        }
        return rs;
    }

    /**
     * 查【Query】
     *
     * @param sql
     * @param obj
     * @return ResultSet
     */
    public static ResultSet executeQuery(String sql, Object... obj) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                pstmt.setObject(i + 1, obj[i]);
            }
            rs = pstmt.executeQuery();
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs, pstmt, conn);
        }
        return rs;
    }

 

    /**
     * 判断记录是否存在
     *
     * @param sql
     * @return Boolean
     */
    public static Boolean isExist(String sql) {
        ResultSet rs = null;
        try {
            rs = executeQuery(sql);
            rs.last();
            int count = rs.getRow();
            if (count > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs);
            return false;
        } finally {
            free(rs);
        }
    } 

    /**
     * 判断记录是否存在
     *
     * @param sql
     * @return Boolean
     */
    public static Boolean isExist(String sql, Object... obj) {
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, obj);
            rs.last();
            int count = rs.getRow();
            if (count > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException err) {
            err.printStackTrace();
            free(rs);
            return false;
        } finally {
            free(rs);
        }
    }

 

    /**
     * 获取查询记录的总行数
     *
     * @param sql
     * @return int
     */
    public static int getCount(String sql) {
        int result = 0;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql);
            rs.last();
            result = rs.getRow();
        } catch (SQLException err) {
            free(rs);
            err.printStackTrace();
        } finally {
            free(rs);
        }
        return result;
    }

 

    /**
     * 获取查询记录的总行数
     *
     * @param sql
     * @param obj
     * @return int
     */
    public static int getCount(String sql, Object... obj) {
        int result = 0;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, obj);
            rs.last();
            result = rs.getRow();
        } catch (SQLException err) {
            err.printStackTrace();
        } finally {
            free(rs);
        }
        return result;
    } 

    /**
     * 释放【ResultSet】资源
     *
     * @param rs
     */
    public static void free(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException err) {
            err.printStackTrace();
        }
    } 

    /**
     * 释放【Statement】资源
     *
     * @param st
     */
    public static void free(Statement st) {
        try {
            if (st != null) {
                st.close();
            }
        } catch (SQLException err) {
            err.printStackTrace();
        }
    }

 

    /**
     * 释放【Connection】资源
     *
     * @param conn
     */
    public static void free(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException err) {
            err.printStackTrace();
        }
    } 

    /**
     * 释放所有数据资源
     *
     * @param rs
     * @param st
     * @param conn
     */
    public static void free(ResultSet rs, Statement st, Connection conn) {
        free(rs);
        free(st);
        free(conn);
    }
}