On the eve of the start of the "Java QA Automation Engineer" course, we have prepared a translation of useful material.
We also invite you to participate in the open webinar on the topic “HTTP. Postman, Newman, Fiddler (Charles), curl, SOAP. SoapUI " . In this lesson, the participants, together with an expert, will analyze what APIs are and how they can check that the backend is returning the expected data, as well as get acquainted with the basic tools for testing.
, , . - . , . , . , 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».