En vísperas del inicio del curso "Java QA Automation Engineer" , hemos preparado una traducción de material útil.
También lo invitamos a participar en el seminario web abierto sobre el tema “HTTP. Cartero, Newman, Fiddler (Charles), rizo, jabón. SoapUI " . En esta lección, los participantes, junto con un experto, analizarán qué son las API y cómo pueden comprobar que el backend está devolviendo los datos esperados, además de familiarizarse con las herramientas básicas para realizar pruebas.
, , . - . , . , . , Spring JdbcTemplate MySQL Java.
MySQL, TestProject, , .
, - . , . , Maven, , pom.xml
, :
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
, , — Spring. JdbcTemplate
, . . MySQL.
: ( Maven). mysql-connector-java MySQL, . , MySQL > 8, «mysql-connector-java» 8.
, . , , . . , , , . , , :
public DataSource mysqlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://dbURL:portNumber/nameOfDB?useSSL=false");
dataSource.setUsername("username");
dataSource.setPassword("password");
return dataSource;
}
, , , DataSource
. JdbcTemplate
, , .
«com.mysql.cj.jdbc.Driver
». , MySQL «com.mysql.jdbc.Driver
». , .
setUrl
. URL-, . , , , setUsername
setPassword
.
, , JdbcTemplate
. :
private JdbcTemplate jdbcTemplate;
@BeforeAll
, , :
jdbcTemplate = new JdbcTemplate(nameOfClass.mysqlDataSource());
, , (updating) (querying) .
Update
JdbcTemplate
. , ‘update’, , . ( ), , , : SQL- String.
: ‘meal’ () ‘ingredient’ (). ‘meal’ , (, , ) id
(primary key). :
jdbcTemplate.update("create table meal(\n" +
" meal_id bigint auto_increment primary key,\n" +
" name varchar(50) not null unique,\n" +
" category varchar(50) not null\n" + ");");
, . . , : . — SQL- update
:
jdbcTemplate.update("insert into meal (name, category) values ('Chicken Fajita', 'lunch');");
jdbcTemplate.update("insert into meal (name, category) values ('Enchilada', 'lunch');");
, update
SQL-.
ingredient
. . (foreign key), meal_id
meal
. , meal
. . , ingredient
(name
), (quantity
) (‘uom’ - unit of measure
) .
, , update
, SQL-:
jdbcTemplate.update("create table ingredient(\n" +
" meal_id bigint not null,\n" +
" name varchar(50) not null,\n" +
" quantity bigint not null,\n" +
" uom varchar(50) not null\n" + ");");
jdbcTemplate.update("alter table ingredient add foreign key (meal_id)" +
" references meal(meal_id);\n");
, ingredient
:
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity,"
+ " uom) values ((select meal_id from meal where name = 'Chicken Fajita'), 'chicken', 1, 'kg');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity, uom) " +
"values ((select meal_id from meal where name = 'Chicken Fajita'), 'red pepper', 1, 'piece');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity, uom) " +
"values ((select meal_id from meal where name = 'Chicken Fajita'), 'green pepper', 1, 'piece');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity, uom) " +
"values ((select meal_id from meal where name = 'Chicken Fajita'), 'yellow pepper', 1, 'piece');");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity," + " uom) " +
"values ((select meal_id from meal where name = " + "'Enchilada'), 'chicken', 1, 'kg');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity," + " uom) " +
"values ((select meal_id from meal where name = " + "'Enchilada'), 'cheese', 100, 'grams');\n");
jdbcTemplate.update("insert into ingredient (meal_id, name, quantity," + " uom) " +
"values ((select meal_id from meal where name = " + "'Enchilada'), 'tomato', 1, 'piece');\n");
, 2 , . JdbcTemplate
.
queryForObject —
, queryForObject
. , :
jdbcTemplate.queryForObject(String sqlStatement, Class returnType);
, (Class
). , , String
( String.class
) ( Integer.class
).
, meal_id
meal
‘Chicken Fajita’. int
:
int id = jdbcTemplate.queryForObject("select meal_id from meal
where name='Chicken Fajita';", Integer.class);
, Integer.class
, int
. , :
System.out.println("Meal id for Chicken Fajita = " + id);
:
Meal id for Chicken Fajita = 1
queryForMap —
, . . queryForMap
, SQL-:
jdbcTemplate.queryForMap(String sqlStatement);
Map
. map
, . , .
id 1
‘meal’, . :
Map<String, Object> entireRowAsMap = jdbcTemplate.queryForMap("select * from meal where meal_id = 1");
System.out.println("All details of meal with id 1 = " + entireRowAsMap);
, entireRowAsMap
Map
, — String
, — Object
. , , — , , Java. :
All details of meal with id 1 = {meal_id=1, name=Chicken Fajita, category=lunch}
queryForList —
, , queryForList
. , SQL- . , (List) Java. , , , , Integer.class
. :
jdbcTemplate.queryForList(String sqlStatement, Class returnType);
Java , ‘ingredient’. . :
List<String> queryForColumn = jdbcTemplate.queryForList("select " +
"distinct name from ingredient", String.class);
System.out.println("All available ingredients = " + queryForColumn);
String
, queryForList
— String.class
. :
All available ingredients = [chicken, red pepper, green pepper, yellow pepper, cheese, tomato]
queryForList —
queryForList
— . , , — SQL-, . map
, map
String
Object
. :
jdbcTemplate.queryForList(String sqlStatement);
‘meal’, .
List<Map<String, Object>> severalRowsAsListOfMaps = jdbcTemplate.queryForList("select * from meal;");
System.out.println("All available meals = " + severalRowsAsListOfMaps);
map
:
All available meals = [{meal_id=1, name=Chicken Fajita, category=lunch}, {meal_id=2, name=Enchilada, category=lunch}]
SQL- . , id
. id
DataProvider
. , id
.
, , ?
. SQL-.
, ingredient
, . int
. :
Integer howManyUsages = jdbcTemplate.queryForObject("select count(*) "
+ "from ingredient where name=?", Integer.class, ingredientToLookFor);
System.out.println("How many time does the ingredient passed as "
+ "parameter appear in the DB " + " = " + howManyUsages);
, queryForObject
, — , — , DataProvider
. , ingredientToLookFor
‘chicken’, :
How many time does the ingredient passed as parameter appear in the DB = 2
Java
Java , ? JdbcTemplate
(Object). , , — Java ; (row mapper
), ; , .
, , , ‘yellow’, (Ingredient Object
). , , , . . Java Ingredient. :
public int meal_id;
public String name;
public int quantity;
public String uom;
. , . , equals
, hashCode
toString
. .
- , , . IntelliJ, Alt+Insert. :
public void setMeal_id ( int meal_id){
this.meal_id = meal_id;
}
public void setName (String name){
this.name = name;
}
public void setQuantity ( int quantity){
this.quantity = quantity;
}
public void setUom (String uom){
this.uom = uom;
}
. , . :
public class IngredientRowMapper implements RowMapper<Ingredient> {
@Override
public Ingredient mapRow(ResultSet rs, int rowNum) throws SQLException {
Ingredient ingredient = new Ingredient();
ingredient.setMeal_id(rs.getInt("meal_id"));
ingredient.setName(rs.getString("name"));
ingredient.setQuantity(rs.getInt("quantity"));
ingredient.setUom(rs.getString("uom"));
return ingredient;
}
}
, RowMapper
. - mapRow
. , . , , quantity
setQuantity
, , quantity
.
IntegerRowMapper
, , queryForObject
, (‘yellow’) :
Ingredient ingredient = jdbcTemplate.queryForObject("select * from "
+ "ingredient where name like '%yellow%'", new IngredientRowMapper());
System.out.println("The ingredient object = " + ingredient);
:
The ingredient object = Ingredient{meal_id=1, name='yellow pepper', quantity=1, uom='piece'}
: — JdbcTemplate
, . , , , SQL-.
"Java QA Automation Engineer".
«HTTP. Postman, Newman, Fiddler (Charles), curl, SOAP. SoapUI».