java-JDBC

必备开头

public static void practice03() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

---

连接报错：Public Key Retrieval is not allowed
url上加 allowPublicKeyRetrieval=true
预编译PreparedStatement
PreparedStatement和Statement都用于执行sql语句，PreparedStatement有预编译机制所以性能比Statement快。

PreparedStatement的setObject（key，value）方法和setString相同，key是整数1、2、3、4等，它表示第几个问号，第几个问号的值。

update

String sql1="update pass set password = ? where number=?";
		try(Connection c=DriverManager.getConnection("jdbc:mysql://localhost/eclipse-database?characterEncoding&useSSL=false","root","12345678");
				PreparedStatement s=c.prepareStatement(sql1);){
				s.setString(1,"3");
				s.setString(2,"3");
				s.execute();
		}catch(SQLException e) {
			e.printStackTrace();
		}

insert

String sql1="insert into pass values(null,?)";
	try(Connection c=DriverManager.getConnection("jdbc:mysql://localhost/eclipse-database?characterEncoding&useSSL=false","root","12345678");
			PreparedStatement s=c.prepareStatement(sql1);){
			s.setString(1,"password");//这个1指的是password
			s.execute();
	}catch(SQLException e) {
		e.printStackTrace();
	}

select

String sql="select *from pass where number = ?";
try(Connection c=DriverManager.getConnection("jdbc:mysql://localhost/eclipse-database?characterEncoding&useSSL=false","root","12345678");
						PreparedStatement s=c.prepareStatement(sql);){
						
						s.setString(1, num);//设置的是第二列的值
						ResultSet set=s.executeQuery();
						while(set.next()) {
							String sql_password=set.getString(2);//第三列的值
						}
					}catch(SQLException e) {
						e.printStackTrace();
					}
求总数：select count(*) from hero

delete

String sqll="delete from hero where  id=?";
        try (Connection connection =getconnection();
             PreparedStatement ps = connection.prepareStatement(sqll);
        ) {
            ps.setInt(1, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

---

编译

try (Connection c=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8","root", "admin");
            Statement s = c.createStatement();) {

        String sqlInsert = "insert into Hero values (null,'盖伦',616,100)";
        String sqlDelete = "delete from Hero where id = 100";
        String sqlUpdate = "update Hero set hp = 300 where id = 100";
        String sqlSelect = "update Hero set hp = 300 where id < 100";
        // 相同点：都可以执行增加，删除，修改

        s.execute(sqlInsert);
        s.execute(sqlDelete);
        s.execute(sqlUpdate);
        s.executeUpdate(sqlInsert);
        s.executeUpdate(sqlDelete);
        s.executeUpdate(sqlUpdate);

    } catch (SQLException e) {
        e.printStackTrace();
    }

execute&execute
不同1：
execute可以执行查询语句
然后通过getResultSet，把结果集取出来
executeUpdate不能执行查询语句
不同2:
execute返回boolean类型，true表示执行的是查询语句，false表示执行的是insert,delete,update等等
executeUpdate返回的是int，表示有多少条数据受到了影响