連結並操作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());