Simple command line database client
- Clone repository
- Run mvn install
Prepare database configuration file. It can contain multiple database configurations:
[
{
"name": "company database",
"driverClassName": "org.postgresql.Driver",
"url": "jdbc:postgresql://localhost:5432/company",
"username": "postgres",
"password": "admin"
}
]
-
You can run dbClient from target folder using:
java -jar dbClient-jar-with-dependencies.jar -c config.json -n "company database" -s "select * from employee"
Result:
[{"id":1,"employee_name":"John Smith","salary":5000.0},{"id":2,"employee_name":"Mambo Jumbo","salary":9000.0}]
-
To prettify output add option -p
java -jar dbClient-jar-with-dependencies.jar -c config.json -n "company database" -s "select * from employee" -p
[ {
"id" : 1,
"employee_name" : "John Smith",
"salary" : 5000.0
}, {
"id" : 2,
"employee_name" : "Mambo Jumbo",
"salary" : 9000.0
} ]
-c * Database configurations JSON file path
-n * Database configuration name
-s * Sql query
-p Prettify output
You can run DbClient programmatically
//define configuration file and configuration name
File configFile = new File("config.json");
String configName = "company database";
//create DbClient
DbConfig dbConfig = new DbConfigFinder(configFile).find(configName);
DbClient dbClient = new DbClient(dbConfig);
//execute SQL query
String sql = "select * from employee";
List<Map<String, Object>> employees = dbClient.query(sql).asList();
//where the map key is the column name
//or
String employees = dbClient.query(sql).asString();
int count = dbClient.query(sql).count();
String result = dbClient.query(sql).asString();
List<Map<String, Object>> records = dbClient.query(sql).asList();
List<Employee> employees = dbClient.query(sql).asList(Employee.class);
dbClient.closeConnection();
You can also map records to List of desirable object.
String sql = "select * from employee";
List<Employee> employees = dbClient.query(sql).asList(Employee.class);
Prepare Employee class with @Data annotation and add fields with names corresponding to column names
import lombok.Data;
@Data
public class Employee {
private Long id;
private String firstname;
private String lastname;
private BigDecimal salary;
}
If you want to have a different name for your field add @Column("column_name") annotation
@Data
public class Employee {
private Long id;
@Column("firstname")
private String name;
@Column("lastname")
private String surname;
private BigDecimal salary;
}
You can also implement your own mapping using RecordMapper class and @Mapper(Class<? extends RecordMapper>) annotation Create EmployeeMapper class:
import pl.pg.dbclient.mapper.RecordMapper;
public class EmployeeMapper extends RecordMapper<Employee> {
@Override
public Employee mapRow(ResultSet resultSet, int i) throws SQLException {
Employee employee = new Employee();
employee.setId(resultSet.getLong("id"));
employee.setName(resultSet.getString("firstname"));
employee.setSurname(resultSet.getString("lastname"));
employee.setSalary(resultSet.getBigDecimal("salary"));
return employee;
}
}
Then add created mapper to Employee class. Simple mapping and Column mapping will be ignored.
@Data
@Mapper(EmployeeMapper.class)
public class Employee {
...
public class EmployeeTest {
private static DbClient dbClient;
@BeforeAll
static void prepareConnection() {
String configPath = "company_dbConfig.json";
String configName = "company database";
File configFile = new File(configPath);
DbConfig dbConfig = new DbConfigFinder(configFile).find(configName);
dbClient = new DbClient(dbConfig);
}
@Test
void shouldMapRecordsToGivenClass() {
String sql = "select * from employee";
List<Employee> employees = dbClient.query(sql).asList(Employee.class);
Employee jack = employees.stream()
.filter(employee -> employee.getName().equals("Jack")).findFirst().get();
assertEquals(jack.getSurname(), "Nicholson");
assertEquals(jack.getSalary(), new BigDecimal("5000.00"));
Employee rob = employees.stream()
.filter(employee -> employee.getName().equals("Rob")).findFirst().get();
assertEquals(rob.getSurname(), "Smith");
assertEquals(rob.getSalary(), new BigDecimal("8000.00"));
}
@AfterAll
static void closeConnection() {
dbClient.closeConnection();
}
}