連結並操作mariadb,
使用maven, 所以在 pom.xml裡加上
<!-- JDBC API --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client --> <dependency> <groupId>org.mariadb.jdbc</groupId> <artifactId>mariadb-java-client</artifactId> <version>3.1.2</version> </dependency>
application.preoperties
# Set here configurations for the database connection spring.datasource.url=jdbc:mariadb://localhost:3306/demo spring.datasource.username=USERNAME spring.datasource.password=PASSWORD spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
在mariadb裡有一table 名為user, 分兩個欄位, name及passwd
所以建立User的物件
User.java
public class User {
private String name;
private String passwd;
public User() {
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
}
使用RowMapper對應到資料庫中的 user資料表
public class UserRowMapper implements RowMapper<User> {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setName(rs.getString("name"));
user.setPasswd(rs.getString("passwd"));
return user;
}
}
操作
//create
String sql = "INSERT INTO user values (:name,:passwd)";
Map<String, String> params = new HashMap<>();
params.put("name", "john");
params.put("passwd", "123456");
namedParameterJdbcTemplate.update(sql, params);
//read
List<User> users = jdbcTemplate.query("SELECT * FROM user", new UserRowMapper());
users.forEach(user -> {
System.out.println(user.getName()+","+user.getPasswd());
});
//query
String sql = "SELECT * FROM user WHERE name=(:name)";
Map<String, String> queryparams = new HashMap<>();
queryparams.put("name", "igogo");
users = namedParameterJdbcTemplate.query(sql, queryparams, new UserRowMapper());
