Datasource MSSQL, PostgreSQL dan Oracle dalam Lucee

Datasource MSSQL, PostgreSQL dan Oracle dalam Lucee

Lucee menyambung ke Microsoft SQL Server, PostgreSQL dan Oracle dengan cara yang sama seperti MySQL: anda mendaftarkan satu datasource dengan kelas pemacu JDBC, rentetan sambungan dan nama pengguna. Perbezaan antara ketiga-tiganya hanya pada pemacu yang dipasang dan bentuk rentetan sambungan. Artikel ini memberikan kedua-duanya, tetapan kolam sambungan, masalah biasa yang anda akan temui, dan cara menulis SQL yang boleh berpindah antara ketiga-tiga pangkalan data tanpa menulis semula pertanyaan.

Kalau anda belum biasa dengan konsep asas datasource, baca dahulu konfigurasi datasource dalam Lucee yang merangkumi Lucee Administrator, Application.cfc dan .CFConfig.json. Artikel itu berfokus kepada MySQL; artikel ini melengkapkannya untuk tiga pangkalan data perusahaan.

Pemacu JDBC yang diperlukan dan dari mana mendapatkannya

Lucee tidak membundel pemacu bagi ketiga-tiga pangkalan data ini secara lalai. Anda perlu mendapatkannya daripada salah satu daripada tiga tempat: sambungan Lucee, repositori Maven secara langsung, atau halaman muat turun vendor. Sejak Lucee 6.1, cara paling bersih ialah meminta Lucee menarik pemacu terus dari Maven dengan menetapkan bundleName dan bundleVersion dalam definisi datasource, sama seperti yang dilakukan untuk pemacu MySQL.

Pemacu Microsoft datang melalui sambungan Lucee org.lucee.mssql atau terus dari Maven. Pemacu PostgreSQL ialah jar postgresql 42.x, dengan nama bundle OSGi org.postgresql.jdbc. Pemacu Oracle ialah ojdbc11, tidak dibundel dengan Lucee, jadi anda mesti meletakkan jar itu dalam /lucee-server/bundles/ atau meminta Lucee menariknya dari Maven; versinya mesti sepadan dengan Java Lucee, iaitu ojdbc11 dan bukannya ojdbc8.

Rentetan sambungan untuk setiap pangkalan data

Contoh berikut menggunakan satu nama datasource kedai untuk ketiga-tiga kes, supaya anda boleh menyalin satu dan menukar medan yang perlu sahaja.

Microsoft SQL Server:

this.datasources["kedai"] = {
    class: "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    bundleName: "org.lucee.mssql",
    bundleVersion: "12.2.0.jre11",
    connectionString: "jdbc:sqlserver://db1.contoh.my:1433;databaseName=kedai;encrypt=true;trustServerCertificate=false",
    username: "app_kedai",
    password: "rahsia_aplikasi"
};

PostgreSQL:

this.datasources["kedai"] = {
    class: "org.postgresql.Driver",
    bundleName: "org.postgresql.jdbc",
    bundleVersion: "42.7.3",
    connectionString: "jdbc:postgresql://db1.contoh.my:5432/kedai",
    username: "app_kedai",
    password: "rahsia_aplikasi"
};

Oracle:

this.datasources["kedai"] = {
    class: "oracle.jdbc.OracleDriver",
    connectionString: "jdbc:oracle:thin:@//db1.contoh.my:1521/kedaipdb",
    username: "app_kedai",
    password: "rahsia_aplikasi"
};

Dua butiran dalam rentetan di atas sering menjadi punca kegagalan. Pada SQL Server, encrypt=true bersama trustServerCertificate=false ialah tetapan lalai pemacu baharu, dan ia bermaksud pemacu menuntut sijil yang sah; kalau pelayan anda menggunakan sijil sendiri, anda mesti sama ada mengimport sijil itu atau menetapkan trustServerCertificate=true semasa pembangunan. Pada Oracle, bentuk @//host:port/perkhidmatan menggunakan nama perkhidmatan, manakala bentuk lama @host:port:SID menggunakan SID; dua nilai itu tidak sama, dan memasukkan SID di tempat nama perkhidmatan ialah ralat yang kerap berlaku.

Tetapan kolam sambungan

Kolam sambungan dikongsi antara ketiga-tiga pangkalan data kerana ia diurus oleh Lucee, bukan oleh pangkalan data. Kunci yang sama yang anda guna untuk MySQL terpakai di sini: connectionLimit mengehadkan bilangan sambungan maksimum dalam kolam, connectionTimeout menetapkan tempoh menunggu sambungan sebelum menyerah, dan validationQuery ialah pertanyaan ringan untuk mengesahkan sambungan masih hidup sebelum digunakan.

Nilai validationQuery berbeza mengikut pangkalan data. Untuk SQL Server dan PostgreSQL, SELECT 1 sudah memadai. Untuk Oracle, pertanyaan itu mesti berbentuk SELECT 1 FROM dual, kerana Oracle tidak membenarkan SELECT tanpa klausa FROM. Ini juga sebab halaman ujian yang ditulis untuk MySQL gagal serta-merta pada Oracle walaupun sambungannya sihat.

Had kolam Lucee mesti sejajar dengan had pelayan pangkalan data, sama seperti yang dibincangkan dalam artikel sambungan MySQL. Kalau kolam membuka lebih banyak sambungan daripada yang dibenarkan pelayan, anda akan mendapat ralat sambungan ditolak walaupun nama datasource dan kata laluan betul.

Masalah biasa dan pembetulannya

Pemacu tidak dijumpai. Mesej seperti "class not found" atau kegagalan memuatkan kelas pemacu bermaksud jar pemacu tidak ada pada classpath. Bezakan ini daripada ralat "datasource tidak dijumpai", yang bermaksud nama datasource belum ditakrifkan; penjelasan lengkap ada dalam artikel ralat datasource tidak ditemui. Untuk pemacu yang hilang, pastikan sambungan dipasang, atau bundleName dan bundleVersion ditetapkan supaya Lucee menarik jar dari Maven.

Sijil SSL. Pemacu SQL Server dan PostgreSQL yang baharu boleh menuntut TLS secara lalai. Pada SQL Server, encrypt=true bersama trustServerCertificate=false menolak sijil yang tidak ditandatangani oleh pihak berkuasa yang dipercayai. Pada PostgreSQL, parameter sslmode mengawal tingkah laku ini, dan sslmode=require menuntut TLS. Kalau sambungan gagal dengan ralat sijil, sama ada import sijil pelayan ke dalam truststore Java atau longgarkan tetapan itu semasa pembangunan sahaja, bukan dalam produksi.

Zon waktu. Ketiga-tiga pangkalan data menyimpan cap masa mengikut tetapan pelayan, dan pemacu menterjemah antara zon waktu aplikasi dan pangkalan data. Kalau tarikh anda beralih beberapa jam, selaraskan zon waktu melalui parameter sambungan atau tetapan timezone pada datasource Lucee, dan pastikan ia sama di semua persekitaran.

Enkod aksara. PostgreSQL menggunakan UTF-8 secara lalai, jadi jarang menjadi masalah. SQL Server menyimpan Unicode hanya dalam lajur NVARCHAR, bukan VARCHAR, jadi lajur teks yang akan memegang nama atau emoji mesti diisytiharkan NVARCHAR. Oracle perlu pangkalan data bercharset AL32UTF8, dan pemacu nipis JDBC mengendalikan pertukaran aksara secara automatik selagi pangkalan data itu sendiri dicipta dengan charset yang betul.

Reserved word. Perkataan seperti user, order dan desc ialah kata simpanan dalam ketiga-tiga pangkalan data. Menggunakannya sebagai nama lajur memaksa anda menulis tanda petik khusus dialek setiap kali, dan tanda petik itu tidak mudah alih. Cara paling selamat ialah elakkan kata simpanan sebagai nama lajur dan jadual dari awal.

Had saiz. Jenis teks panjang berbeza antara ketiga-tiganya. SQL Server mempunyai VARCHAR(MAX), PostgreSQL mempunyai TEXT, dan Oracle menghadkan VARCHAR2 kepada 4000 bait secara lalai sebelum memerlukan CLOB. Untuk teks panjang yang boleh melebihi 4000 aksara, gunakan CLOB pada Oracle atau jenis teks tanpa had pada dua yang lain, dan jangan andaikan VARCHAR boleh memegang segalanya.

Menguji sambungan sebelum aplikasi bergantung padanya

Jangan biarkan halaman pertama aplikasi menjadi ujian sambungan anda. Dua cara mengesahkan sebelum kod sebenar bergantung pada datasource: butang verify dalam Lucee Administrator pada halaman datasource, dan pertanyaan ujian dalam kod.

<cfscript>
try {
    q = queryExecute(
        "SELECT 1 FROM dual",
        [],
        { datasource: "kedai" }
    );
    writeOutput("Sambungan OK");
} catch (any e) {
    writeOutput("Gagal: " & e.message);
}
</cfscript>

Contoh di atas menggunakan FROM dual supaya ia berfungsi pada Oracle. Untuk SQL Server dan PostgreSQL, SELECT 1 tanpa FROM sudah cukup. Uji dengan pertanyaan yang sama bentuknya dengan beban sebenar aplikasi, kerana sambungan yang lulus untuk SELECT 1 belum tentu lulus untuk pertanyaan dengan CLOB atau lajur NVARCHAR. Gunakan cfqueryparam dalam pertanyaan sebenar supaya nilai pengguna tidak dicantum terus ke dalam SQL.

Menulis SQL mudah alih antara ketiga-tiganya

Matlamat SQL mudah alih ialah menulis pertanyaan sekali dan menjalankannya pada ketiga-tiga pangkalan data tanpa suntingan. Tiga peraturan menjimatkan kebanyakan kerja.

Gunakan fungsi standard SQL yang disokong ketiga-tiganya, iaitu COALESCE, CASE dan CAST, dan elakkan fungsi khusus vendor. Untuk cap masa semasa, gunakan CURRENT_TIMESTAMP dan bukannya GETDATE() pada SQL Server, NOW() pada PostgreSQL atau SYSDATE pada Oracle, kerana CURRENT_TIMESTAMP wujud dalam ketiga-tiganya.

Untuk penomboran halaman, dialek berbeza: SQL Server menggunakan SELECT TOP 10, PostgreSQL menggunakan LIMIT 10, dan Oracle 12c ke atas menggunakan FETCH FIRST 10 ROWS ONLY. Cara paling mudah alih ialah menyerahkan tugas itu kepada Lucee melalui atribut maxrows pada cfquery, yang diterjemahkan mengikut dialek datasource.

<cfquery name="qProduk" datasource="kedai" maxrows="10">
    SELECT id, nama, harga
    FROM produk
    ORDER BY id
</cfquery>

Elakkan mencantum rentetan di dalam SQL, kerana operatornya berbeza: PostgreSQL dan Oracle menggunakan ||, manakala SQL Server menggunakan +. Buat pencantuman di CFML dan serahkan nilai siap kepada cfqueryparam. Akhir sekali, untuk prosedur tersimpan, sintaks panggilan juga berbeza, dan Lucee menyediakan cfstoredproc dan cfprocparam untuk menangani perbezaan itu secara seragam.

Soalan Lazim

Pemacu mana yang saya perlukan untuk Oracle pada Lucee moden?

Gunakan oracle.jdbc.OracleDriver dengan jar ojdbc11 untuk Java 11 ke atas. Jar itu tidak dibundel dengan Lucee, jadi tarik ia dari Maven (com.oracle.database.jdbc:ojdbc11) atau letak sebagai bundle OSGi dalam /lucee-server/bundles/.

Kenapa pertanyaan ujian SELECT 1 gagal pada Oracle?

Oracle tidak membenarkan SELECT tanpa klausa FROM. Gunakan SELECT 1 FROM dual. Ini juga sebab validationQuery untuk datasource Oracle mesti berbentuk SELECT 1 FROM dual.

Kenapa sambungan SQL Server saya ditolak walaupun nama dan kata laluan betul?

Kemungkinan besar pemacu baharu menuntut TLS, dan sijil pelayan tidak dipercayai. Import sijil ke truststore Java, atau semasa pembangunan sahaja tetapkan trustServerCertificate=true dalam rentetan sambungan.

Bagaimana saya menyimpan emoji dalam SQL Server?

Gunakan lajur NVARCHAR, bukan VARCHAR, kerana VARCHAR tidak menyimpan Unicode. Pastikan nilai dihantar dengan jenis aksara yang betul melalui cfqueryparam.

Bolehkah saya menulis satu pertanyaan untuk ketiga-tiga pangkalan data?

Ya, jika anda berpegang kepada fungsi standard seperti COALESCE, CASE, CAST dan CURRENT_TIMESTAMP, mengelakkan kata simpanan sebagai nama lajur, dan menggunakan maxrows atau cfqueryparam Lucee untuk menangani perbezaan dialek.

Sumber

• Datasources, docs.lucee.org
• Get Datasource Drivers Directly from Maven, docs.lucee.org
• Database Connection Management, docs.lucee.org
• OracleDriver, Oracle Database JDBC Java API Reference
• Connecting to MSSQL Server using the latest drivers, Lucee Dev

Senarai semak

• Pasang atau tarik pemacu JDBC yang betul untuk pangkalan data anda, dan sahkan ia sepadan dengan versi Java Lucee.
• Gunakan kelas pemacu dan rentetan sambungan yang tepat bagi SQL Server, PostgreSQL atau Oracle.
• Tetapkan connectionLimit dan validationQuery, dengan SELECT 1 FROM dual untuk Oracle.
• Uji sambungan dengan pertanyaan ujian sebelum kod aplikasi bergantung padanya.
• Semak sijil SSL, zon waktu dan enkod aksara sebelum berurusan dengan prestasi.
• Elakkan kata simpanan sebagai nama lajur dan gunakan jenis teks panjang yang betul.
• Tulis SQL standard dan serahkan perbezaan dialek kepada maxrows, cfqueryparam dan cfstoredproc.