JDBC与mysql数据库操作

java数据库连接

	    // 注册MySQL驱动 (可以省略这一步)
        //Class.forName("com.mysql.jdbc.Driver");

        // 连接MySQL服务器
        String username= "root";
        String password = "19970304";
        String connectionUrl = "jdbc:mysql://localhost:3306/db_name?useUnicode=true&characterEncoding=UTF-8";

        Connection conn = DriverManager.getConnection(connectionUrl, username, password);
        conn.close();   //关闭连接

JDBC查询数据

		//数据库查询，Statement语句，ResultSet结果集
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM student");
        //如果有数据，rs.next()返回true
        while (rs.next()){
            int id = rs.getInt(1);     //按列序号获取
            //int id = rs.getInt("id");  //按列标题获取
            String name = rs.getString(2);
            String phone = rs.getString(3);
            Date birthday = rs.getDate(4);

            System.out.println(id+name+phone+birthday);
        }

JDBC插入数据

	String sql = "INSERT INTO student (`id`,`name`,`birthday`) VALUES ('20180030','韩','1990-3-5')";
	Statement stmt = conn.createStatement();
	stmt.execute(sql);   //插入

	int count = stmt.getUpdateCount();    //可查看受影响的行为数
	System.out.println("受影响的行为数："+count);

【UPDATE、DELETE都是一样写好sql语句再stmt.execute(sql)】

处理自增主键

插入时不用制定自增字段值，以下方法可以获得自动生成的主键值

	//返回自动生成的的主键值
	stmt.execute(sql,Statement.RETURN_GENERATED_KEYS);
	//取得自动生成的主键值
	ResultSet rs = stmt.getGeneratedKeys();
	while(rs.next()){
		int id = rs.getInt(1);
		System.out.println(id);
	}

预处理查询

        String sql = "INSERT INTO student (id,name,birthday) VALUES(?,?,?)";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setInt(1,20183001);
        ptmt.setString(2,"小新");
        ptmt.setString(3,"2000-03-05");
        ptmt.execute();

插多条记录时采用

	String sql = "...";
	PreparedStatement ptmt = conn.prepareStatement(sql);
	while(...){
		ptmt.set(1,...);
		ptmt.set(2,...);
		ptmt.execute();
	}

元数据MetaData

即字段的描述信息

		ResultSet rs = stmt.executeQuery("SELECT * FROM student");

        //显示元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        int numColumn = rsmd.getColumnCount();
        for(int i=1;i<=numColumn;i++){    //序列号1，2，3
            String name = rsmd.getColumnName(i);   //列名
            String label = rsmd.getColumnLabel(i);   //列标题（别名）
            int type = rsmd.getColumnType(i);  //类型，参考java.sql.Tyoes定义
            String typeName = rsmd.getColumnTypeName(i); //类型名称

            System.out.printf("第%d列:%s,%s,%s\n",i,name,label,typeName);
        }