准备数据
CREATE TABLE `t_subject` (
`su_id` int NOT NULL AUTO_INCREMENT,
`su_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`su_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3;
/*Data for the table `t_subject` */
insert into `t_subject`(`su_id`,`su_name`) values
(1,'语文'),
(2,'数学');
JDBC 取出自增主键
本篇主要聊的是得到自增主键的数据,所以只是聊取自增主键时候遇见的问题,如果想来更好的理解JDBC 可以看前面文章:传送阵
首先本人用的是mysql8.0版本,所以得到连接connection如下:
public static Connection getConnection() throws SQLException {
Driver driver = new Driver();//com.mysql.cj.jdbc.Driver
String url = "jdbc:mysql://localhost:3307/testmybatis?useSSL=FALSE&serverTimezone=Asia/Shanghai&characterEncoding=utf8&autoReconnect=TRUE&useUnicode=TRUE";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "root");
Connection connection = driver.connect(url, info);
return connection;
}
}
如按照一般取出的时候,不会遇见类似的问题:
如下:
public static void testJDBC(){
Connection connection=getConnection();
String sql="SELECT su_id , su_name FROM testmybatis.t_subject ";
PreparedStatement preparedStatement= connection.prepareStatement(sql);
try {
ResultSet resultSet= preparedStatement.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString(1),resultSet.getString(2));
}
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
似乎是没有问题,但是如果插入数据后想得到主键:
public static void testJDBC(){
Connection connection=getConnection();
String sql="INSERT INTO `testmybatis`.`t_subject` (`su_id`,`su_name`) VALUES ( null,'地理'); ";
PreparedStatement preparedStatement= connection.prepareStatement(sql);
try {
preparedStatement.executeUpdate();
ResultSet resultSet= preparedStatement.getGeneratedKeys();;
while (resultSet.next()){
System.out.println(resultSet.getString(1) );
}
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
这个报错是因为默认是拒绝访问自动主键。可以这样设置:
// 添加一个参数
PreparedStatement preparedStatement= connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
因为测试前面插入两个条数据,所以这个数据返回的值是5;
mybatis 自增主键
这个直接看操作吧,就不多说什么了
public interface T_subjectMapper {
void insertSuject(T_subject t_subject);
}
<mapper namespace="com.xzd.mapper.T_subjectMapper">
<!-- 这个配置文件id和 mapper类中的方法一样-->
<insert id="insertSuject" useGeneratedKeys="true" keyProperty="su_id">
INSERT INTO `testmybatis`.`t_subject` (`su_id`,`su_name`) VALUES ( null,#{su_name})
</insert>
</mapper>
因是插入不会返回主键这个值,所以通过传入的是一个T_subject对象,而通过这个对象得到自增主键,结果如下