package nl.astraeus.database import nl.astraeus.database.annotations.Column import nl.astraeus.database.annotations.Id import nl.astraeus.database.annotations.Table import nl.astraeus.database.jdbc.ConnectionPool import nl.astraeus.database.jdbc.ConnectionProvider import org.junit.Assert.* import org.junit.Before import org.junit.Test import java.sql.Connection import java.sql.DriverManager /** * User: rnentjes * Date: 18-10-15 * Time: 17:00 */ @Table data class Company(var name: String) { @Id var id: Long = 0 protected constructor(): this("") fun users(): List<User> { return transaction<List<User>> { var dao = UserDao() dao.where("company = ?", id) } } } @Table(name = "usr") class User( var company: Company, var name: String, var email: String) { @Id var id: Long = 0 protected constructor(): this(Company(""), "", "") } // needs manual index to prevent double entries @Table class ManyToMany(var company: Company, @Column(name = "usr") var user: User) { @Id var id: Long = 0 protected constructor(): this(Company(""), User(Company(""), "", "")) } class CompanyDao(): SimpleDao<Company>(Company::class.java) class UserDao(): SimpleDao<User>(User::class.java) class MTMDao(): SimpleDao<ManyToMany>(ManyToMany::class.java) { fun users(comp: Company): List<User> { return transaction<List<User>> { val dao = UserDao() dao.from("join manytomany where manytomany.user = usr.id and manytomany.company = ?", comp.id) } } fun companies(user: User): List<Company> { return transaction<List<Company>> { val dao = CompanyDao() dao.from("join manytomany where manytomany.company = company.id and manytomany.user = ?", user.id) } } } fun createConnection(): Connection { Class.forName("org.h2.Driver") val connection = DriverManager.getConnection("jdbc:h2:mem:TestQueries", "sa", "") connection.autoCommit = false // result return connection } class MyConnectionProvider : ConnectionProvider() { override fun getConnection() = createConnection() override fun getDefinition(): DdlMapping.DatabaseDefinition { return DdlMapping.DatabaseDefinition.H2 } } class TestQueries { @Before fun setUp() { val db = SimpleDatabase.define(ConnectionPool(MyConnectionProvider())) db.setExecuteDDLUpdates(true); } @Test fun testWhere() { val companyDao = CompanyDao() val userDao = UserDao() val mtmDao = MTMDao() transaction { val company = Company("company") val info = User(company, "Info", "info@somewhere.com") val piet = User(company, "Piet", "piet@somewhere.com") userDao.insert(info) userDao.upsert(piet) info.name = "Iiiinfo" userDao.update(info) piet.email = "pietje@somewhere.com" userDao.upsert(piet) mtmDao.insert(ManyToMany(company, info)) mtmDao.insert(ManyToMany(company, piet)) mtmDao.insert(ManyToMany(Company("Other company"), info)) } transaction { val user = userDao.find("name = ?", "Iiiinfo") if (user != null) { user.company.name = "Better Company!" companyDao.update(user.company) val companies = mtmDao.companies(user) for (company in companies) { println("Company from ${user.name} -> ${company.name}") } } } transaction { val found = userDao.where("name = ?", "Iiiinfo") assertTrue(found.size == 1) assertTrue(userDao.all().size == 2) assertTrue(userDao.count("name = ?", "Piet") == 1) for(company in companyDao.all()) { println("Company: #${company.id} - ${company.name}") for(user in company.users()) { println("Company user: #${user.id} - ${user.name} - ${user.email} - ${user.company.name}") } } for(user in userDao.all()) { println("Found: #${user.id} - ${user.name} - ${user.email} - ${user.company.name}") } var rs = query(query = "SELECT * FROM company") while(rs.next()) { print("Company: ") print(rs.getLong(1)) print(" - ") println(rs.getString(2)) } rs = query(query = "SELECT * FROM usr") while(rs.next()) { print("User: ") print(rs.getLong(1)) print(" - ") print(rs.getLong(2)) print(" - ") print(rs.getString(3)) print(" - ") println(rs.getString(4)) } rs = query(query = "SELECT * FROM manytomany") while(rs.next()) { print("MTM: ") print(rs.getLong(1)) print(" - ") print(rs.getLong(2)) print(" - ") println(rs.getLong(3)) } } } }