CODEWITHSUNDEEP
javasqlpostgresqlormlite
Fausto70
Fausto70

Reputation: 551

How to execute raw SQL on DB (not on specific table)

I use PostgreSQL and ORMlite in my application, that is runned on a Windows server. Before creating all the tables, I need to verify if the database is existing in Postgres, and if not I have to create it.

I already have my SQL Raw:

QUESTION: How to exec that using ORMLite? I know that to exec a raw SQL I can use: 

myDAO.executeRaw(SQL);

but here it is not on a specific table, so I cannot use it because I cannot create myDAO.

Upvotes: 0

Views: 1394

Answers (2)

sungyong
sungyong

Reputation: 2499

I also agree that it is not good practice.
But your intent is possible in ormLite.
You can use raw query any DAO. DAO is not strictly bounded in table.

Refer my sample codes.

sqls.xml : it contains raw query. You can define any raw query.

<item name="sql_login" type="string">
    SELECT AdminPubTBL.*
    FROM PDARunPubTBL
    JOIN AdminPubTBL ON PDARunPubTBL.AdminSeqNo = AdminPubTBL.AdminSeqNo
    .....
      AND AdminPubTBL.AdminPwd = \'@adminPwd\' LIMIT 1
</item>

adminDao.java 

public interface AdminDao {
    Admin login(int pdaSeqNo, int sectionSeqNo, String adminId, String adminPwd, String corpCode);

adminDaoOrmLite.java : You can use queryRaw and return to any Java object.

public class AdminDaoOrmLite implements AdminDao {
    @Nullable
    private DatabaseHelper databaseHelper = null;
    @Nullable
    private Dao<Admin, Integer> adminDao = null;
    public Admin login(int pdaSeqNo, int sectionSeqNo, @NonNull String adminId, String adminPwd, String corpCode) {
        String sql = context.getResources().getString(R.string.sql_login)
                .replace("@pdaSeqNo", String.valueOf(pdaSeqNo))
                .replace("@sectionSeqNo", String.valueOf(sectionSeqNo))
                .replace("@adminId", adminId)
                .replace("@adminPwd", hashedPassword(adminPwd,corpCode));
        try {
            return adminDao.queryRaw(sql, adminDao.getRawRowMapper()).getFirstResult();
        } catch (SQLException e) {
            LOG.error(e);
        }
    }

So if you want to want raw query. Make common DAO, then execute .queryRaw()

Upvotes: 1

Pau Trepat
Pau Trepat

Reputation: 717

I have not looked for it, but there might be other (better) solutions other than yours.

Generally, it is not a good approach to couple the application and the infrastructure. Your PostgreSql is part of your infrastructure and your application should not care whether your infrastructure is set.

A better approach would consist in splitting your infrastructure definition, probably using a simple Bash script or (ideally) something like Terraform and don't couple your application from your infrastructure.

Upvotes: 1

Related Questions