JDBC连接MySQL

目的:学会JDBC，参考自imooc的视频教程。

         用到的工具Navicat for MySQL一个非常好的MySQL操作软件。注意事项:要记住MySQL安装时候的用户名和密码。

         还有一点可能要注意的就是电脑上装的MySQL要处于运行状态,才去Run As Java Application。

         用到mysql-connector-java-xxx-bin.jar这个驱动。

DEMO结构:                                                                     数据库imooc的表player的结构如下:

                     

先用Navicat创建好数据库和表,以命令行形式。用命令行介面:

create database imooc;
create table player(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int,
    birthday date
   )engine=innodb default charset=utf8 auto_increment=1;
alter database testDB default character set 'utf8';
SET　 charater_set_client='utf8';
SET　 charater_set_connection='utf8';
SET　 charater_set_results='utf8';

ENGINE=InnoDB 表示将数据库的引擎设置为InnoDB,从MySQL 5.6开始默认使用该引擎。

DEFAULT CHARSET=utf8 表示设置数据库的默认字符集为utf8

AUTO_INCREMENT=1   表示自动增长的起始值为1

代码一: BDUtil.java  ，连接数据库的类。其实应该命名为DBUtil才对==|||||

public class BDUtil {
    private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc";
    //3306为mysql端口号,imooc为数据库名字
    private static final String USER="root";
    private static final String PASSWORD="root";
    
    public static Connection conn;
	public BDUtil() { }
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn=(Connection) DriverManager.getConnection(URL,USER,PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	public static Connection getConnection(){
		return conn;
	}
	public static void main(String[] args)throws Exception {
        //这里的main()仅为了测试数据库能否连接正常而已
		Statement stmt=(Statement) conn.createStatement();
		String sql="select * from player";
		stmt.executeQuery(sql);
		//引入的是java.sql.ResultSet 不是驱动里的ResultSet
		ResultSet rs=stmt.getResultSet();
		Player p=null;
		List<Player> pList=new ArrayList<Player>();
		while(rs.next()){
			System.out.println("name="+rs.getString("name")+"--age="+rs.getInt("age"));
		}
	}
}

代码二: Dao.java

public class Dao {

	public Dao() {
	}
	public void addPlayer(Player player) throws SQLException{
		Connection conn=BDUtil.getConnection();
		String sql="insert into player "
				+ "(name,age)"+"values(?,?)";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, player.getName());
		ptmt.setInt(2, player.getAge());
		ptmt.execute();
	}
	public void delPlayer(Integer id) throws SQLException{
		Connection conn=BDUtil.getConnection();
		String sql="delete from player"+
				" where id=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1,id);
		ptmt.execute();
	}
	public void updatePlayer(Player p) throws SQLException{
		Connection conn=BDUtil.getConnection();
		String sql="update player"+
		        " set name=?,age=? "+
				"where id=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setString(1, p.getName());
		ptmt.setInt(2, p.getAge());
		ptmt.setInt(3,p.getId());
		ptmt.execute();
	}
    //查询全部
    public ArrayList<Player> query() throws SQLException{
    	Connection conn=BDUtil.getConnection();
    	Statement stmt=(Statement) conn.createStatement();
		String sql="select * from player";
		stmt.executeQuery(sql);
		//引入的是java.sql.ResultSet 不是驱动里的ResultSet
		ResultSet rs=stmt.getResultSet();
		
		Player p=null;
		ArrayList<Player> pList=new ArrayList<Player>();
		while(rs.next()){
			System.out.println("name="+rs.getString("name")+"--age="+rs.getInt("age"));
			p =new Player();
			p.setName(rs.getString("name"));
			p.setAge(rs.getInt("age"));
			pList.add(p);
		}
    	return pList;
    }
    //根据ID查询单个
    public Player get(Integer id) throws SQLException{
		Connection conn=BDUtil.getConnection();
		String sql="select * from  player "
				+ " where id=?";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		ptmt.setInt(1, id);
//		ptmt.execute(); 增删改用execute() 查询用executeQuery();
		ResultSet rs=ptmt.executeQuery();
		Player p=null;
		while(rs.next()){
			p=new Player();
			p.setName(rs.getString("name"));
			p.setAge(rs.getInt("age"));
			p.setId(rs.getInt("id"));
		}
    	return p;
    }
    //根据名字查询,返回数组(重名情况)
    public ArrayList<Player> query(String name) throws SQLException{
    	Connection conn=BDUtil.getConnection();
    	String sql="select * from player "
    			+" where name=?";
    	PreparedStatement ptmt=conn.prepareStatement(sql);
    	ptmt.setString(1, name);//%为通配符
    	ResultSet rs=ptmt.executeQuery();
    	Player p=null;
    	ArrayList<Player> pList=new ArrayList<Player>();
    	while(rs.next()){
    		p=new Player();
			p.setName(rs.getString("name"));
			p.setAge(rs.getInt("age"));
			p.setId(rs.getInt("id"));
			pList.add(p);
    	}
		return pList;
    }
    //自动匹配传入多个参数查询
    public ArrayList<Player> query(List<Map<String,Object>>params) throws SQLException{
    	Connection conn=BDUtil.getConnection();
    	StringBuilder sb=new StringBuilder();
    	sb.append("select * from player where 1=1");//1=1是免得第一个也出现and
    	if(params!=null&¶ms.size()>0){
    		for(int i=0;i<params.size();i++){
    			Map<String,Object> map=params.get(i);
    			sb.append(" and "+map.get("name")+" "+map.get("rela")+map.get("value"));
    			//rela可以为各种通配符
    		}
    	}
    	PreparedStatement ptmt=conn.prepareStatement(sb.toString());
    	ResultSet rs=ptmt.executeQuery();
    	Player p=null;
    	ArrayList<Player> pList=new ArrayList<Player>();
    	while(rs.next()){
    		p=new Player();
			p.setName(rs.getString("name"));
			p.setAge(rs.getInt("age"));
			p.setId(rs.getInt("id"));
			pList.add(p);
    	}
		return pList;
    }
}

代码三: PlayAction.java ，演示类运行其中main()方法

public class PlayAction {

	public PlayAction() {
	}
    public static void main(String[] args) throws SQLException {
		Dao dao=new Dao();
//		查询
//		List<Player> pList=dao.query();
//		for(Player p:pList){
//			System.out.println(p.getName()+"--"+p.getAge());
//		}
//		添加
//		Player p=new Player();
//		p.setName("Odom");
//		p.setAge(36);
//		dao.addPlayer(p);
//		更新
//		Player p=new Player();
//		p.setName("Jeremy_Lin");
//		p.setAge(28);
//		p.setId(3);
//		dao.updatePlayer(p);
//		删除
//      dao.delPlayer(3);
//		查询单个
//		Player p=dao.get(2);
//		System.out.println(p.toString());
//		根据name查询
//		ArrayList<Player> pList=dao.query("kobe");
//		System.out.println(pList.size()+"");
//		for(int i=0;i<pList.size();i++){
//			Player p=pList.get(i);
//			System.out.println(p.toString());
//		}
//		多参数自动匹配查询
        List<Map<String,Object>> params=new ArrayList<Map<String,Object>>();
        Map<String,Object> param=new HashMap<String,Object>();
        param.put("name", "name");
        param.put("rela", "=");
        param.put("value", "'kobe'");//记住这里必须加''，但为什么?_?
//        param.put("name", "name");
//        param.put("rela", "like");
//        param.put("value", "'%o%'");//%为通配符
        
//        param.put("age", "age");
//        param.put("rela", "=");
//        param.put("value", "'36'");//通过Age去查询
        
        params.add(param);
		ArrayList<Player> pList=dao.query(params);
		System.out.println(pList.size()+"");
		for(int i=0;i<pList.size();i++){
			Player p=pList.get(i);
			System.out.println(p.toString());
		}
    }
}

代码四: Player.java ,JavaBean类

public class Player {
    private String name;
    private int age;
    private int id;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	public Player() {
	}
    @Override
    public String toString() {
    	return "name="+this.getName()+"--age="+this.getAge()+"--id="+this.getId();
    }
}