From d2eebaa772a22c15b26810ce9e17785fcbd9b01f Mon Sep 17 00:00:00 2001 From: Olaf Wintermann Date: Fri, 5 Sep 2025 18:09:35 +0200 Subject: [PATCH] fix database connection cleanup --- .../kotlin/de/unixwork/rssreader/Database.kt | 249 +++++++++--------- 1 file changed, 131 insertions(+), 118 deletions(-) diff --git a/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt b/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt index b1ab08f..93d6e5b 100644 --- a/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt +++ b/rss-application/src/main/kotlin/de/unixwork/rssreader/Database.kt @@ -20,21 +20,21 @@ object Database { config.maximumPoolSize = 16 dataSource = HikariDataSource(config) - ensureSchema(dataSource.connection) + dataSource.connection.use { + ensureSchema(it) + } } private fun ensureSchema(conn: Connection) { - val stmt = conn.createStatement() - val rs = stmt.executeQuery( - "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FEEDCOLLECTIONS'" - ) - var tableExists = false - if (rs.next()) { - tableExists = rs.getInt(1) > 0 + + conn.createStatement().use { stmt -> + stmt.executeQuery("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'FEEDCOLLECTIONS'").use { rs -> + if (rs.next()) { + tableExists = rs.getInt(1) > 0 + } + } } - rs.close() - stmt.close() if (!tableExists) { println("Database empty: creating tables") @@ -99,8 +99,9 @@ object Database { public fun getFeedTree(context: Context) : MutableList { val groups = mutableListOf() - dataSource.connection.createStatement().use { stmt -> - val rs = stmt.executeQuery(""" + dataSource.connection.use { conn -> + conn.createStatement().use { stmt -> + val rs = stmt.executeQuery(""" select g.group_id, g.name as group_name, @@ -116,31 +117,32 @@ object Database { order by g.pos, f.pos """.trimIndent()) - var currentGroup: FeedGroup? = null - while(rs.next()) { - val groupId = rs.getInt("group_id") - val groupName = rs.getString("group_name") - val feedId = rs.getInt("feedcollection_id") - val feedName = rs.getString("feed_name") - val updateInterval = rs.getLong("update_interval") - val itemStateMode = rs.getInt("item_state_mode") - val unreadCount = rs.getInt("unread_count") - - if(currentGroup == null || currentGroup.id != groupId) { - currentGroup = FeedGroup(context, groupId, groupName) - groups.add(currentGroup) - } + var currentGroup: FeedGroup? = null + while(rs.next()) { + val groupId = rs.getInt("group_id") + val groupName = rs.getString("group_name") + val feedId = rs.getInt("feedcollection_id") + val feedName = rs.getString("feed_name") + val updateInterval = rs.getLong("update_interval") + val itemStateMode = rs.getInt("item_state_mode") + val unreadCount = rs.getInt("unread_count") + + if(currentGroup == null || currentGroup.id != groupId) { + currentGroup = FeedGroup(context, groupId, groupName) + groups.add(currentGroup) + } + + if(feedId != null && feedName != null) { + val feed = FeedCollection(feedId, feedName) + feed.updateInterval = updateInterval + feed.itemStateMode = itemStateMode + feed.unreadItemsCount = unreadCount + currentGroup.feeds.add(feed) + } - if(feedId != null && feedName != null) { - val feed = FeedCollection(feedId, feedName) - feed.updateInterval = updateInterval - feed.itemStateMode = itemStateMode - feed.unreadItemsCount = unreadCount - currentGroup.feeds.add(feed) } - + rs.close() } - rs.close() } return groups @@ -148,16 +150,18 @@ object Database { public fun newFeedGroup(context: Context, name: String) : FeedGroup { var groupId = 0 - dataSource.connection.prepareStatement(""" + dataSource.connection.use { conn -> + conn.prepareStatement(""" insert into groups (pos, name) select coalesce(max(pos), 0)+1, ? from groups - """.trimIndent(), Statement.RETURN_GENERATED_KEYS).use { stmt -> - stmt.setString(1, name) - stmt.execute() - stmt.generatedKeys.use { rs -> - if(rs.next()) { - groupId = rs.getInt(1) - } else { - throw Exception("Insert Group failed") + """.trimIndent(), Statement.RETURN_GENERATED_KEYS).use { stmt -> + stmt.setString(1, name) + stmt.execute() + stmt.generatedKeys.use { rs -> + if(rs.next()) { + groupId = rs.getInt(1) + } else { + throw Exception("Insert Group failed") + } } } } @@ -175,40 +179,43 @@ object Database { itemStateMode: Int = 0) : FeedCollection { var feedcollectionId = -1 - val connection = dataSource.connection - connection.prepareStatement(""" + var feedCol: FeedCollection + dataSource.connection.use { connection -> + connection.prepareStatement(""" insert into feedcollections (group_id, pos, name, update_interval, item_state_mode) select ?, coalesce(max(pos), 0)+1, ?, ?, ? from groups - """.trimIndent(), Statement.RETURN_GENERATED_KEYS).use { stmt -> - stmt.setInt(1, parent.id) - stmt.setString(2, name) - stmt.setLong(3, updateInterval) - stmt.setInt(4, itemStateMode) - stmt.execute() - stmt.generatedKeys.use { rs -> - if(rs.next()) { - feedcollectionId = rs.getInt(1) - } else { - throw Exception("Insert FeedCollection failed") + """.trimIndent(), Statement.RETURN_GENERATED_KEYS).use { stmt -> + stmt.setInt(1, parent.id) + stmt.setString(2, name) + stmt.setLong(3, updateInterval) + stmt.setInt(4, itemStateMode) + stmt.execute() + stmt.generatedKeys.use { rs -> + if(rs.next()) { + feedcollectionId = rs.getInt(1) + } else { + throw Exception("Insert FeedCollection failed") + } } } - } - var feedCol = FeedCollection(feedcollectionId, name) + feedCol = FeedCollection(feedcollectionId, name) - uris.forEach { uri -> - connection.prepareStatement(""" + uris.forEach { uri -> + connection.prepareStatement(""" insert into feeds (feedcollection_id, url, auth_user, auth_password, certpath) values (?, ?, ?, ?, ?) """.trimIndent()).use { stmt -> - stmt.setInt(1, feedcollectionId) - stmt.setString(2, uri) - stmt.setString(3, user) - stmt.setString(4, password) - stmt.setString(5, cert) + stmt.setInt(1, feedcollectionId) + stmt.setString(2, uri) + stmt.setString(3, user) + stmt.setString(4, password) + stmt.setString(5, cert) - stmt.execute() + stmt.execute() + } } } + parent.feeds.add(feedCol) return feedCol } @@ -216,32 +223,34 @@ object Database { public fun getItems(feedCollection: FeedCollection, maxItems: Int) : MutableList { val items = mutableListOf() - dataSource.connection.prepareStatement(""" + dataSource.connection.use { conn -> + conn.prepareStatement(""" select I.*, F.URL from items I inner join feeds F on I.feed_id = F.feed_id where F.feedcollection_id = ? order by pub_date desc limit ? - """.trimIndent()).use { stmt -> - stmt.setInt(1, feedCollection.id) - stmt.setInt(2, maxItems) - stmt.executeQuery().use { rs -> - while(rs.next()) { - val item = Item(rs.getInt("item_id")) - item.feedId = rs.getInt("feed_id") - item.title = rs.getString("title") - item.link = rs.getString("link") - item.category = rs.getString("category") - item.description = rs.getString("description") - item.author = rs.getString("author") - item.pubDate = rs.getObject("pub_date", java.time.LocalDateTime::class.java) - item.updated = rs.getObject("updated", java.time.LocalDateTime::class.java) - item.guid = rs.getString("guid") - item.contentText = rs.getString("contentText") - item.contentHtml = rs.getString("contentHTML") - item.feedName = feedCollection.name - item.feedUrl = rs.getString("URL") - item.isRead = rs.getBoolean("is_read") - item.isBookmark = rs.getBoolean("is_bookmarked") - items.add(item) + """.trimIndent()).use { stmt -> + stmt.setInt(1, feedCollection.id) + stmt.setInt(2, maxItems) + stmt.executeQuery().use { rs -> + while(rs.next()) { + val item = Item(rs.getInt("item_id")) + item.feedId = rs.getInt("feed_id") + item.title = rs.getString("title") + item.link = rs.getString("link") + item.category = rs.getString("category") + item.description = rs.getString("description") + item.author = rs.getString("author") + item.pubDate = rs.getObject("pub_date", java.time.LocalDateTime::class.java) + item.updated = rs.getObject("updated", java.time.LocalDateTime::class.java) + item.guid = rs.getString("guid") + item.contentText = rs.getString("contentText") + item.contentHtml = rs.getString("contentHTML") + item.feedName = feedCollection.name + item.feedUrl = rs.getString("URL") + item.isRead = rs.getBoolean("is_read") + item.isBookmark = rs.getBoolean("is_bookmarked") + items.add(item) + } } } } @@ -251,22 +260,24 @@ object Database { public fun getAllFeeds() : MutableList { val feeds = mutableListOf() - dataSource.connection.prepareStatement(""" + dataSource.connection.use { conn -> + conn.prepareStatement(""" select * from feeds - """.trimIndent()).use { stmt -> - stmt.executeQuery().use { rs -> - while(rs.next()) { - val id = rs.getInt("feed_id") - val feedCollectionId = rs.getInt("feedcollection_id") - val url = rs.getString("url") - val authUser = rs.getString("auth_user") - val authPassword = rs.getString("auth_password") - val certPath = rs.getString("certpath") - val feed = Feed(id, feedCollectionId, url) - feed.user = authUser - feed.password = authPassword - feed.certpath = certPath - feeds.add(feed) + """.trimIndent()).use { stmt -> + stmt.executeQuery().use { rs -> + while(rs.next()) { + val id = rs.getInt("feed_id") + val feedCollectionId = rs.getInt("feedcollection_id") + val url = rs.getString("url") + val authUser = rs.getString("auth_user") + val authPassword = rs.getString("auth_password") + val certPath = rs.getString("certpath") + val feed = Feed(id, feedCollectionId, url) + feed.user = authUser + feed.password = authPassword + feed.certpath = certPath + feeds.add(feed) + } } } } @@ -277,23 +288,25 @@ object Database { public fun getPendingFeeds() : MutableList { val feeds = mutableListOf() - dataSource.connection.prepareStatement(""" + dataSource.connection.use { conn -> + conn.prepareStatement(""" select f.* from feeds f inner join feedcollections c on f.feedcollection_id = c.feedcollection_id where datediff(ss, coalesce(last_update, '1970-01-01'), now()) > case when c.update_interval > 0 then c.update_interval else 60 end - """.trimIndent()).use { stmt -> - stmt.executeQuery().use { rs -> - val id = rs.getInt("feed_id") - val feedCollectionId = rs.getInt("feedcollection_id") - val url = rs.getString("url") - val authUser = rs.getString("auth_user") - val authPassword = rs.getString("auth_password") - val certPath = rs.getString("certpath") - val feed = Feed(id, feedCollectionId, url) - feed.user = authUser - feed.password = authPassword - feed.certpath = certPath - feeds.add(feed) + """.trimIndent()).use { stmt -> + stmt.executeQuery().use { rs -> + val id = rs.getInt("feed_id") + val feedCollectionId = rs.getInt("feedcollection_id") + val url = rs.getString("url") + val authUser = rs.getString("auth_user") + val authPassword = rs.getString("auth_password") + val certPath = rs.getString("certpath") + val feed = Feed(id, feedCollectionId, url) + feed.user = authUser + feed.password = authPassword + feed.certpath = certPath + feeds.add(feed) + } } } -- 2.47.3