package com.cnblogs.chuanyueinlife;

 import java.sql.Connection;

 import java.sql.DriverManager;

 import java.sql.PreparedStatement;

 import java.sql.ResultSet;

 import java.sql.SQLException;

 /**

  * JAVA链接MySQL数据库的步骤示例:

  *

  * @author 张建(chuanyueinlife)

  *

  */

 public class JdbcTest {

     static Connection conn = null;

     static PreparedStatement pst = null;

     public static void main(String[] args) {

         // 实例1.查询操作:

         String sql = "SELECT * FROM `user` WHERE user_name =? AND pass_word = ?";// 拼sql语句(有防注入占位符)

         selectDemo(sql);

         // 实例2.删除

         String sql1 = "DELETE FROM `user` WHERE user_name = 'zj'";

         deleteDemo(sql1);

     }

     public static void deleteDemo(String sql) {

         getConn();

         getPst(sql);

         try {

             int num = pst.executeUpdate();

             System.out.println("成功删除了" + num + "条记录!");

         } catch (SQLException e) {

             // TODO Auto-generated catch block

             e.printStackTrace();

         }

         closeAll();// 释放资源

     }

     public static void selectDemo(String sql) {

         getConn();

         getPst(sql);

         try {

             pst.setString(1, "admin");

             pst.setString(2, "admin");

             ResultSet rs = pst.executeQuery();

             System.out.println("用户ID\t用户名\t密码");

             while (rs.next()) {

                 System.out

                         .println(rs.getInt("id") + "\t" + rs.getString("user_name") + "\t" + rs.getString("pass_word"));

             }

         } catch (SQLException e) {

             // TODO Auto-generated catch block

             e.printStackTrace();

         }

         closeAll();// 释放资源

     }

     public static void getConn() {

         /*

          * 配置数据库信息:

          */

         String driver = "com.mysql.jdbc.Driver";// 驱动

         String url = "jdbc:mysql://localhost:3306/zj";// 数据库url

         String user = "root";// 数据库用户名

         String pass = "123456";// 数据库密码

         try {

             Class.forName(driver);// 加载驱动

             conn = DriverManager.getConnection(url, user, pass);// 获得连接对象

         } catch (ClassNotFoundException | SQLException e) {

             // TODO Auto-generated catch block

             e.printStackTrace();

         }

     }

     public static void getPst(String sql) {

         try {

             pst = conn.prepareStatement(sql);// 预处理sql

         } catch (SQLException e) {

             // TODO Auto-generated catch block

             e.printStackTrace();

         }

     }

     public static void closeAll() {

         try {

             pst.close();

             conn.close();

         } catch (SQLException e) {

             // TODO Auto-generated catch block

             e.printStackTrace();

         }

     }

 }