JDBC连接mysql的几种方式

实现JDBC的三种法（记录）

第一种方法（开发推荐）：

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

public class JDBC_preparestatement {
    private static final String url="jdbc:mysql://localhost:3306/javadb";
    private static final  String username = "root";
    private static final String password = "";
    public static void update() throws ClassNotFoundException, SQLException {
        //a，导入驱动，加载具体的驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //b，与数据库建立连接
        Connection connection = DriverManager.getConnection(url,username,password);
        //c，发送sql语句
        String sql = "insert into student values(?,?,?,?);";//开发推荐使用
        PreparedStatement stmt = connection.prepareStatement(sql);
        stmt.setInt(1, 81);
        stmt.setString(2, "小黑");
        stmt.setInt(3, 22);
        stmt.setInt(4, 100);
        int count = stmt.executeUpdate();
        //d，处理结果集
        if(count>0){
            System.out.println("操作成功");
        }else{
            System.out.println("操作失败");
        }
        //e，关闭对象
        stmt.close();
        connection.close();
    }
    public static void select() throws ClassNotFoundException, SQLException{
        //导入驱动，加载具体的驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //与数据库建立连接
        Connection connection = DriverManager.getConnection(url,username,password);
        
        //写查询的sql语句
        String sql ="SELECT * from student;";
        //创建statement事务执行sql语句
        PreparedStatement stmt = connection.prepareStatement(sql);
        //使用事务对象的executeQuery获取结果集
        ResultSet rs = stmt.executeQuery(sql);
        // 处理结果集,但指针的下一行有数据时，继续循环，否则停止。
        while(rs.next()){
        	//使用rs.getXxx("字段名")来获取对象
        	int sno = rs.getInt("id");
        	String name = rs.getString("name");
        	//对结果进行打印
        	System.out.println(sno+"---"+name);
        }
        //最后一个开的第一个关
        rs.close();
        stmt.close();
        //第一个开的最后一个关
        connection.close();
    }
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //select();
        update();
    }
}

第二种方法（比较规范）：

package aaa;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Later {
    private static final String url="jdbc:mysql://localhost:3306/javadb";
    private static final  String username = "root";
    private static final String password = "";
    public static void update() throws ClassNotFoundException, SQLException {
        //a，导入驱动，加载具体的驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //b，与数据库建立连接
        Connection connection = DriverManager.getConnection(url,username,password);
        //c，发送sql语句
        Statement stmt = connection.createStatement();    
        String sql = "show DATABASES;";
        int count = stmt.executeUpdate(sql);//返回值表示增删改几条数据
        //d，处理结果集
        if(count>0){
            System.out.println("操作成功");
        }else{
            System.out.println("操作失败");
        }
        //e，关闭对象
        stmt.close();
        connection.close();
    }
    public static void select() throws ClassNotFoundException, SQLException{
        //导入驱动，加载具体的驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //与数据库建立连接
        Connection connection = DriverManager.getConnection(url,username,password);
        //创建statement事务执行sql语句
        Statement stmt = connection.createStatement();
        //写查询的sql语句
        String sql ="SELECT * from student;";
        //使用事务对象的executeQuery获取结果集
        ResultSet rs = stmt.executeQuery(sql);
        // 处理结果集,但指针的下一行有数据时，继续循环，否则停止。
        while(rs.next()){
        	//使用rs.getXxx("字段名")来获取对象
        	int sno = rs.getInt("id");
        	String name = rs.getString("name");
        	//对结果进行打印
        	System.out.println(sno+"---"+name);
        }
        //最后一个开的第一个关
        rs.close();
        stmt.close();
        //第一个开的最后一个关
        connection.close();
    }
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        select();
        //update();
    }
}

第三种方法（用于测试）：

package util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBHelper {
	private static final String driver = "com.mysql.jdbc.Driver";//数据库连接
	private static final String url = "jdbc:mysql://localhost:3306/javadb?useUnicode=true&characterEncoding=UTF-8";
	private static final String username = "root";
	private static final String password = "";
	private static Connection conn=null;
	//静态代码块负责加载驱动
	static{
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static Connection getConnection() throws SQLException{
		if(conn==null){
			conn = DriverManager.getConnection(url,username,password);
			return conn;
		}
		return conn;
	}
	
	public static void main(String[] args) {
		try {
			Connection conn = DBHelper.getConnection();
			if(conn!=null){
				System.out.println("数据库连接正常");
			}else {
				
				System.out.println("数据库连接失败");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

sql注入演示：

源sql语句：

select count(*) from student where id='"+username+"' and age='"+passwordString+"' 

输入（–后面有空格）：

任意值 ‘ or 1=1 -- 

sql注入后：select count(*) from student where id='asdfads ' or 1=1                 -- ' and age='"+passwordString+"'

sql注入：将客户端输入的内容和开发人员的sql语句混为一体.