Home Php C# Sql C C++ Javascript Python Java Go Android Git Linux Asp.net Django .net Node.js Ios Xcode Cocoa Iphone Mysql Tomcat Mongodb Bash Objective-c Scala Visual-studio Apache Elasticsearch Jar Eclipse Jquery Ruby-on-rails Ruby Rubygems Android-studio Spring Lua Sqlite Emacs Ubuntu Perl Docker Swift Amazon-web-services Svn Html Ajax Xml Java-ee Maven Intellij-idea Rvm Macos Unix Css Ipad Postgresql Css3 Json Windows-server Vue.js Typescript Oracle Hibernate Internet-explorer Github Tensorflow Laravel Symfony Redis Html5 Google-app-engine Nginx Firefox Sqlalchemy Lucene Erlang Flask Vim Solr Webview Facebook Zend-framework Virtualenv Nosql Ide Twitter Safari Flutter Bundle Phonegap Centos Sphinx Actionscript Tornado Register | Login | Edit Tags | New Questions | 繁体 | 简体


10 questions online user: 28

0
votes
answers
31 views
+10

遷飛JDBC連接遷移麻煩

0


我有遷飛JDBC遷移麻煩:執行我試圖關閉數據庫連接與conn.close();方法,而且所有報表後,我收到org.postgresql.util.PSQLException: This connection has been closed.例外。當我檢查數據庫時 - 我在遷移過程中所做的所有更改都已完成,但schema_version表未更新:未添加有關遷移的項目。
更多細節在這裏:
在flyway.properties我添加了classpath:com/packageone/somepackege/database/migrationflyway.locations部分。然後,我創建了將執行遷移類:public class V1_1_1__SomeImportantMigration implements JdbcMigration並填寫migrate方式類似:即在migrate方法做
遷飛JDBC連接遷移麻煩

@Override 
     public void migrate(Connection conn) throws Exception { 
      beforeActions(conn); 
      veryImportantMigration(conn, getData(conn)); 
      afterActions(conn); 
      conn.close(); 
     } 

事情被應用,但關閉連接導致異常。
由於上述信息,我有這樣的問題:爲什麼這個連接在被我關閉之前關閉?並通過什麼?

UPDATE(堆棧跟蹤):

[ERROR] Unable to rollback transaction 
org.postgresql.util.PSQLException: This connection has been closed. 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:820) 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:837) 
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:96) 
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273) 
    at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:68) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.lock(PostgreSQLDbSupport.java:162) 
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) 
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) 
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464) 
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971) 
    at org.flywaydb.maven.MigrateMojo.doExecute(MigrateMojo.java:30) 
    at org.flywaydb.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:600) 
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80) 
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) 
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193) 
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106) 
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863) 
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288) 
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:199) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    at java.lang.reflect.Method.invoke(Method.java:498) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) 
[ERROR] Unable to restore autocommit to original value for connection 
org.postgresql.util.PSQLException: This connection has been closed. 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:820) 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.setAutoCommit(AbstractJdbc2Connection.java:762) 
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:114) 
    at org.flywaydb.core.internal.command.DbMigrate.applyMigrations(DbMigrate.java:273) 
    at org.flywaydb.core.internal.command.DbMigrate.access$800(DbMigrate.java:53) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:210) 
    at org.flywaydb.core.internal.command.DbMigrate$2.call(DbMigrate.java:146) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:68) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.lock(PostgreSQLDbSupport.java:162) 
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) 
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) 
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464) 
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971) 
    at org.flywaydb.maven.MigrateMojo.doExecute(MigrateMojo.java:30) 
    at org.flywaydb.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:600) 
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80) 
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) 
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193) 
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106) 
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863) 
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288) 
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:199) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    at java.lang.reflect.Method.invoke(Method.java:498) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) 
[ERROR] Unable to release Flyway advisory lock 
org.postgresql.util.PSQLException: This connection has been closed. 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:820) 
    at org.postgresql.jdbc3.AbstractJdbc3Connection.prepareStatement(AbstractJdbc3Connection.java:275) 
    at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareStatement(AbstractJdbc2Connection.java:293) 
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.prepareStatement(JdbcTemplate.java:319) 
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:247) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLAdvisoryLockTemplate.execute(PostgreSQLAdvisoryLockTemplate.java:81) 
    at org.flywaydb.core.internal.dbsupport.postgresql.PostgreSQLDbSupport.lock(PostgreSQLDbSupport.java:162) 
    at org.flywaydb.core.internal.metadatatable.MetaDataTableImpl.lock(MetaDataTableImpl.java:174) 
    at org.flywaydb.core.internal.command.DbMigrate.migrate(DbMigrate.java:146) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:1010) 
    at org.flywaydb.core.Flyway$1.execute(Flyway.java:971) 
    at org.flywaydb.core.Flyway.execute(Flyway.java:1464) 
    at org.flywaydb.core.Flyway.migrate(Flyway.java:971) 
    at org.flywaydb.maven.MigrateMojo.doExecute(MigrateMojo.java:30) 
    at org.flywaydb.maven.AbstractFlywayMojo.execute(AbstractFlywayMojo.java:600) 
    at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:134) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:207) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) 
    at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:116) 
    at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:80) 
    at org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder.build(SingleThreadedBuilder.java:51) 
    at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:128) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:307) 
    at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:193) 
    at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:106) 
    at org.apache.maven.cli.MavenCli.execute(MavenCli.java:863) 
    at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:288) 
    at org.apache.maven.cli.MavenCli.main(MavenCli.java:199) 
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) 
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) 
    at java.lang.reflect.Method.invoke(Method.java:498) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:289) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:229) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) 
    at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) 
+1

你說他們是部分完成的。你有沒有例外?你能提供日誌嗎? – Korgen

+2

您不應該關閉該連接,它由飛路擁有。 –

+0

@Korgen,我可以分享日誌。部分惹眼 - 這只是我的錯誤... –

沙发
0
2

你不應該調用由遷飛提供給您的連接上close()。 Flyway將其自身用於其他內務處理任務(包括更新版本表),並關閉它可防止飛路正常工作。

0
votes
answers
44 views
+10

使用TO_CHAR在Oracle中進行數字格式化

0

在ORACLE存儲過程中格式化數字的正確方法。使用TO_CHAR在Oracle中進行數字格式化

我需要顯示2位小數的貨幣字段。 預期輸出如下:

  • 0> 0.00
  • 5> 5.00
  • 1253.6> 1253.60
  • 1253.689> 1253.69

下面爲我工作:

select to_char(9876.23 , 'fm999990.00') from dual; 

但這有硬c的問題編了一堆9。如果我給一個更大的數字,它會顯示爲「##############」

有沒有其他方法可以做到這一點?

+0

對於較大的數字,增加格式說明符中的9。 –

+0

我會很感激,如果下來的選民可以解釋爲什麼...... :( – PAVITRA

沙发
0
2

我需要2位小數,顯示貨幣字段。

確保您使用具有適合數據的比例和精度的數字數據類型,而不使用NUMBER而沒有比例和精度。如果你打算存儲美元/歐元/磅/等。那麼Gross World Product是的$ 100,000,000,000,000在2014年的訂單讓我們假設你是不是將要處理超過此[來源請求]那麼你的貨幣列可以是:

NUMBER(17,2) 

如果你得到一個價值大於此值,那麼您需要對您的數據執行完整性檢查,並考慮一個比世界總產品更大的數額是否合理。如果您要將這些值存儲爲例如日元或津巴布韋元,請適當調整比例。

你甚至可以在一個包中定義一個子類:

CREATE PACKAGE currencies_pkg IS 
    SUBTYPE currency_type IS NUMBER(17,2); 

    FUNCTION formatCurrency(
    amount IN CURRENCY_TYPE 
) RETURN VARCHAR2; 
END; 
/

您的代碼格式化,可以這樣:

CREATE PACKAGE BODY currencies_pkg IS 
    FUNCTION formatCurrency(
    amount IN CURRENCY_TYPE 
) RETURN VARCHAR2 
    IS 
    BEGIN 
    RETURN TO_CHAR(currency_value, 'FM999999999999990D00'); 
    END; 
END; 
/

然後,如果你引用的子類型的存儲過程/包,您將無法超過貨幣數據類型的最大大小,而不會引發異常。顯示值的格式模型只需要在一個地方定義,並且由於輸入限於貨幣子類型,所以格式化函數將永遠不會超過強加的比例/精度,並且不能輸出#

CREATE PROCEDURE your_procedure(
    in_value1 IN ACCOUNTS_TABLE.ACCOUNT_BALANCE%TYPE, 
    in_value2 IN ACCOUNTS_TABLE.ACCOUNT_BALANCE%TYPE 
) 
IS 
    v_value CURRENCIES_PKG.CURRENCY_TYPE; 
BEGIN 
    -- Do something 
    v_value := in_value1 + in_value2; 
    -- Output formatted value 
    DBMS_OUTPUT.PUT_LINE(CURRENCIES_PKG.formatCurrency(v_value)); 
END; 
/
+1

這是一個合理的論據對我來說。 – PAVITRA

板凳
0
1

爲什麼「硬編碼一堆9」是一個問題? (如果你打算使用TO_CHAR,你需要這麼做)

select to_char(9876.23 , 'fm9999999999999999999990D00') from dual; 

ps;你可能要考慮使用D而不是.(不是每個國家使用.作爲小數點分隔 - D是語言敏感,將使用相應的符號)

0
votes
answers
41 views
+10

每個線程一個SQLiteConnection?

6

我使用的是來自system.data.sqlite.org的SQLite每個線程一個SQLiteConnection?

我們需要從多個線程(出於各種原因)訪問數據庫。我讀了很多關於sqlite線程安全功能(默認同步訪問模式對我來說很好)。

我不知道是否可以簡單地打開每個線程的連接。是這樣的可能嗎?我真的不關心比賽條件(請求尚未插入的東西)。我只感興趣的是可以使用每個線程一個對象SQLiteConnection來訪問數據。

沙发
0
3

是的。事實上,這是正確的方式,因爲SQLite不是線程安全的(默認情況下,您可以使用某些選項進行線程安全編譯)。而只是爲了確保它的工作原理:在一些小的網站正在使用SQLite的,所以多線程有:)

這裏的更多信息:http://www.sqlite.org/faq.html#q6

板凳
0
4

由於您使用每個線程一個單獨的連接,你應該罰款。

docs

注意SQLiteConnection實例不保證線程安全 。您應該同時避免在幾個 線程中使用相同的 SQLiteConnection。建議每個線程打開一個新的連接 ,並在工作完成時關閉它。

0
votes
answers
20 views
+10

轉換爲varchar(40)datetime列

1

我有date列,它的類型是varchar(40) 我想將它轉換爲datetime轉換爲varchar(40)datetime列

請讓我知道,如果下面的命令是正確轉換爲datetime

UPDATE `table` 
SET `date` = str_to_date(`date`, '%d-%m-%Y'); 

該表有成千上萬的記錄。例如,date具有價值10/21/2016 15:02一條記錄

沙发
0
1

兩件事情:

首先,你STR_TO_DATE()格式必須輸入字符串的格式相匹配。如果你的輸入字符串是10/21/2016 15:02,那麼你的格式是%m/%d/%Y %H:%i

見這裏的格式代碼的參考:https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

其次,使用更新不會更改列的數據類型,它只是改變了VARCHAR字符串的內容。

mysql> create table t (v varchar(50)); 

mysql> insert into t values ('10/21/2016 15:02'); 

mysql> update t set v = str_to_date(v, '%m/%d/%Y %H:%i'); 

mysql> select * from t; 
+---------------------+ 
| v     | 
+---------------------+ 
| 2016-10-21 15:02:00 | 
+---------------------+ 

現在它的格式正確,但它仍然是一個varchar。

mysql> alter table t modify v datetime; 

mysql> select * from t; 
+---------------------+ 
| v     | 
+---------------------+ 
| 2016-10-21 15:02:00 | 
+---------------------+ 

現在數據類型已更改。

mysql> show create table tG 

CREATE TABLE `t` (
    `v` datetime DEFAULT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
0
votes
answers
27 views
+10

在Oracle中將具有多列的多行轉換爲一個記錄

2

在Oracle SQL查詢中我們得到了40個記錄具有13列。我想合併所有這些記錄到一列意味着40 * 13 = 520列在1記錄。其EG-樣品表記錄數在Oracle中將具有多列的多行轉換爲一個記錄

col1 col2 city cntry conti 
1  abc NYC USA NA 
2  def LON UK  EU 
3  xyz DUB UAE ASIA 

再經過合併所有記錄&進入一個記錄,那麼它應該像下面的單

col1 col2 city cntry conti col1 col2 city cntry conti col1 col2 city cntry conti 
1  abc NYC USA NA  2  def LON UK  EU  3  xyz DUB UAE ASIA 
+0

Oracle對單個記錄中的字節數有限制。您的數據可能不適合放在一行上。 –

+0

@GordonLinoff你能提供一個對這個限制的參考嗎?我[可以找到](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/logical-database-limits.html#GUID-685230CF-63F5-4C5A-B8B0-037C566BDA76)只有每個表的列的限制。 –

+0

「行的固定長度列的最大累積長度(字節)= 32,768」,https://docs.oracle.com/cd/E11882_01/timesten.112/e21643/limit.htm#TTREF456。 –

沙发
0
2

如果列col1包含唯一值那麼你可以使用pivot

select * 
    from t 
    pivot (max(col1) col1, max(col2) col2, max(city) city, max(cntry), max(conti) conti 
     for col1 in (1, 2, 3)) 

SQLFiddle demo

+0

可以用1條記錄中的40 * 13 = 420列來做 – user8487380

+0

[是](http://sqlfiddle.com/#!4/83b2cd/247)。 –

+1

@PonderStibbons可以在Oracle 10g上執行,因爲在Oracle 10g中沒有任何受支持的WITH子句。沒有WITH子句可以合併? –

0
votes
answers
31 views
+10

加入一個表,兩個獨立的一個一對多表

0

我有三個表,就像這樣:加入一個表,兩個獨立的一個一對多表

projects 
| id | name  | 
|----|------------| 
| 1 | enterprise | 
| 2 | discovery | 

widgets 
| project_id | name   | 
|------------|-----------------| 
| 1   | saucer section | 
| 1   | pylons   | 
| 2   | spinning saucer | 
| 2   | angular pylons | 

sprockets 
| project_id | name  | 
|------------|-------------| 
| 1   | engineering | 
| 1   | bridge  | 
| 1   | mess  | 
| 2   | engineering | 
| 2   | bridge  | 
| 2   | mess  | 

我想寫的是,給我正好十個結果的查詢:基本上是一個爲每行widgetssprockets,看起來像這樣:

result 
| project_name | widget_name  | sprocket_name | 
|--------------|-----------------|---------------| 
| enterprise | saucer section | null   | 
| enterprise | pylons   | null   | 
| enterprise | null   | engineering | 
| enterprise | null   | bridge  | 
| enterprise | null   | mess   | 
| discovery | spinning saucer | null   | 
| discovery | angular pylons | null   | 
| discovery | null   | engineering | 
| discovery | null   | bridge  | 
| discovery | null   | mess   | 

相反,我的連接被合併到返回12行。增加一個組似乎減少了太多。

我已經試過類似以下,但連接被乘以:

select 
    p.name as project_name, 
    w.name as widget_name, 
    s.name as sprocket_name 
from 
    projects as p 
left join widgets w on p.id = w.project_id 
left join sprockets s on p.id = s.project_id; 

answersI'vefound主要集中在重新設計的數據庫,但是這不是我的選擇。 如何編寫連接以從此數據集返回上述十行?

沙发
0
3

使用與每個表連接的UNION

SELECT p.name AS project_name, w.name AS widget_name, NULL AS sprocket_name 
FROM projects AS p 
INNER JOIN widgets AS w ON p.id = w.project_id 

UNION ALL 

SELECT p.name AS project_name, NULL AS widget_name, s.name AS sprocket_name 
FROM projects AS p 
INNER JOIN sprockets AS s ON p.id = s.project_id 
+0

這樣做的結果是20行,其中一些數據看起來與我想要的不相關。我錯過了什麼嗎? –

+1

糟糕,忘記了'ON'子句 – Barmar

+0

如果您將「p」添加到「FROM projects AS p」的第二行,我會接受這個答案:) –

0
votes
answers
17 views
+10

Php不工作添加更多行(中繼器)

0

我使用表單中繼器創建發票。發票有多個產品來自數據庫和價格。 當我選擇產品時,其自動價格出現在費用框中,並且還有其他輸入框,如折扣和百分比,默認值爲0 0。第一行工作得很好。但是當我點擊添加更多按鈕來生成下一行添加另一個產品。Php不工作添加更多行(中繼器)

在下一行中,我可以從下拉列表中選擇產品。但其費用沒有出現,其他輸入框值如折扣和百分比也消失(他們沒有顯示任何默認值。)

這是我的HTML代碼。

<div class="row"> 
    <div class="col-xs-3"> 
     <label for="single" class="control-label">Select Invoice Type</label> 
    </div> 
    <div class="col-xs-2">Fee</div> 
    <div class="col-xs-1">Quantity</div> 
    <div class="col-xs-1">Discount</div> 
    <div class="col-xs-2">Total</div> 
    <div class="col-xs-1">Doctor %</div> 
</div> 
<div class="mt-repeater"> 
    <div data-repeater-list="group-b"> 
     <div data-repeater-item class="row"> 
      <div class="col-xs-3"> 
       <div class="form-group"> 
        <select id="invoice" class="form-control" name="invoice"> 
         <option></option> 
         <?php 
          $select = mysqli_query(connection(), "select * from treatement"); 
          while($r = mysqli_fetch_array($select)) { 
           echo "<option value='$r[0]'>$r[name]</option>"; 
          } 
         ?> 
        </select> 
       </div> 
      </div> 
      <div class="col-xs-2"> 
       <div class="form-group"> 
        <input type="text" value="" class="form-control" placeholder="Fee" name="fees" id="fees"> 
       </div> 
      </div> 
      <div class="col-xs-1"> 
       <div class="form-group"> 
        <input type="text" class="form-control" value="1" name="quantity" > 
       </div> 
      </div> 
      <div class="col-xs-1"> 
       <div class="form-group"> 
        <input type="text" value="0" class="form-control" placeholder="Discount" name="discount"> 
       </div> 
      </div> 
      <div class="col-xs-2"> 
       <div class="form-group"> 
        <input type="text" value="0" class="form-control" placeholder="Total" readonly name="total"> 
       </div> 
      </div> 
      <div class="col-xs-2"> 
       <div class="form-group"> 
        <input type="text" value="0" class="form-control" placeholder="%" name="percentage"> 
       </div> 
      </div> 
      <div class="col-md-1"> 
       <a href="javascript:;" data-repeater-delete class="btn btn-danger"> 
       <i class="fa fa-close"></i> 
       </a> 
      </div> 
     </div> 
    </div> 
    <a href="javascript:;" data-repeater-create class="btn btn-info mt-repeater-add"> 
    <i class="fa fa-plus"></i> Add More 
    </a> 
    <br> 
    <br> 
</div> 

而且我使用js函數來比較每個產品的費用。這有助於我在一個盒子裏顯示費用。

$(document).ready(function() { 
    $("#invoice").change(function() { 
     var value = $(this).val(); 
     $.get("get_fees.php", { 
      id: value 
     }, function(data) { 
      $("#fees").val(data); 
     }) 
    }) 
}) 
+1

您是否在每一行使用'id =「invoice」'? ID必須是唯一的,您應該使用類而不是ID。然後請參閱[動態創建的元素上的事件綁定](https:// stackoverflow。com/questions/203198/event-binding-on-dynamic-created-elements),以便爲它們綁定事件處理程序。 – Barmar

+0

注意:'mysqli'的面向對象的接口明顯不那麼冗長,使得代碼更易於閱讀和審計,並且不容易與陳舊的'mysql_query'接口混淆。在你過於投入程序風格之前,它是值得轉換的。例如:'$ db = new mysqli(...)'和'$ db-> prepare(「...」)過程接口是PHP4時代的一個神器,當引入mysqli API時,不應該在新的碼。 – tadman

沙发
0
2

請..不要使用這樣的ID。 ID必須是唯一的,你應該使用類而不是ID(見barmar評論)

<p id="test"></p> 
<p id="test"></p> 
<p id="test"></p> 

應該

<p id="test1" class="test"></p> 
<p id="test2" class="test"></p> 
<p id="test3" class="test"></p> 

你的是,我不使用同一ID,因爲ID必須是唯一的。

  • 1個ID必須由1個元素使用。
  • 1類可以由很多元素

使用,但是,你仍然可以破解這個。 $('select[id="invoice"]')

,似乎你創建新的DOM,你可以改變這一行

$("#invoice").change(function() {

成爲

$("body").on('change', 'select[id="invoice"]', function() {

UPDATE: 爲什麼你總是改變你的第一選擇/對第一個id =費用。因爲你使用$('#fees').val()

必須指定,具有:獲取父(我closest()使它),然後找到您要更改

,因爲你不特定的ID =「費」有識別你的父母中繼anyspecific類,我選擇添加rowsdata類

變化<div data-repeater-item class="row"><div data-repeater-item class="row rowsdata">

和你的腳本將看起來像這樣

$("body").on('change', 'select[id="invoice"]', function() { 
    var select = $(this); 
    $.get("get_fees.php", { 
     id: select.val() 
    }, function(data) { 
     select.closest('.rowsdata').find('input[id="fees"]').val(data); //if you still give id="fees" and dont have any class use this 
     //select.closest('.rowsdata').find('.fees').val(data); if you already give class="fees", use this 
    }); 
}); 
+0

謝謝,當我替換腳本行並將id =「費用」更改爲課程時,它會起作用。但是,當我在第2或第3行更改產品時,它將上述行的費用更改爲與上一行相同。你可以請幫我這個 –

+0

看到我的更新 – plonknimbuzz

+0

以上謝謝。這很好。 :) –

0
votes
answers
35 views
+10

如何在php中創建搜索過濾器

0

我有這個問題與PHP。我想做的事情是建立一個包含三個過濾器的搜索引擎:按標題,描述和價格搜索。 這是我的索引html。如何在php中創建搜索過濾器

<form style="text-align:center;" method="get" action="search.php"> 
    <label> 
    Search 
    <input type="text"name="keywords" autocomplete="off"> 
    </label> 
    <input type="submit" value="Search"><br> 
</form> 

通過使用操作方法,我打電話給search.php文件。

if(isset($_GET['keywords'])) { 
    $keywords = $db->escape_string($_GET['keywords']); 

    $query = $db->query("SELECT title, description, price FROM products WHERE title LIKE '%{$keywords}%' OR description LIKE '%{$keywords}%' OR price LIKE '%{$keywords}%'"); 
} 

不是它結合了所有的結果之一,因爲我使用的是同一個變量的關鍵字。 現在的問題是我沒有過濾器,因爲我不知道如何創建它。也許我希望有一個下拉選擇與這三個選項:標題,描述,價格。但我不知道如何讓我的PHP代碼看看哪一個被選中。或者,也許我的想法有更好的解決方案? 我應該使用不同的變量,如關鍵字是或什麼?

沙发
0
1

這是簡單的..添加一個選擇標籤。這裏是一個例子。

<!doctype html> 
<html> 
<head> 
    <title>filter</title> 
</head> 
<body> 
<form method="post" action="test1.php"> 
    <select name="keywords" > 
    <option value="title">title</option> 
    <option value="description">description</option> 
    <option value="price">price</option> 
    </select> 
    <input type="submit"> 
</form> 

<?php print_r($_POST); 
if(isset($_GET['keywords'])) { 
    $keywords = $db->escape_string($_GET['keywords']); 

    $query = $db->query("SELECT title, description, price FROM products WHERE '%{$keywords}%' LIKE '%{$keywords}%' "); 
} 

?> 
<br> 


</body> 
</html> 
板凳
0
1

是的。除了你必須保護你的代碼注入

if(isset($_GET['keywords']) && isset($_GET['field'])) { 
     $keywords = $db->escape_string($_GET['keywords']); 
     $sql="SELECT title, description, price FROM products WHERE ". $_GET['field'] ." like '%{$keywords}%'" ; 
     $query = $db->query($sql); 
    } 

:您必須添加一個選擇,以限制搜索的領域:

<form style="text-align:center;" method="get" action="search.php"> 
    <label> 
    Search 
    <input type="text"name="keywords" autocomplete="off"> 
    </label> 
<select name="field"> 
    <option value="title">title</option> 
    <option value="description">description</option> 
    <option value="prcie">price</option> 
</select> 

    <input type="submit" value="Search"><br> 
</form> 

和PHP。

+0

感謝解決方案!我該如何保護我的注入代碼?你能給我一些關於它的信息嗎? – CaL17

+1

@ CaL17 [我怎樣才能防止SQL注入在PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) – FirstOne

+0

我猜有些東西是錯誤的這個「。$ _GET ['field']。」。這是一個語法問題或quatation標記問題。它不適合我,我的編輯表明這是不正確的 – CaL17

地板
0
1

拆分輸入字符串以單一關鍵字explode功能

$keywords = "world word2"; 
$keywords_arr = explode(" ",$keywords); 
echo $keywords_arr[0]; 
0
votes
answers
29 views
+10

與-v標誌泊塢窗運行命令使容器在已退出狀態

1

我試圖用-v國旗,但容器的狀態變爲Exited (0) 1映射本地目錄容器/home/ubuntu/data/var/lib/mysql文件夾。但是,如果我根本不使用-v標誌,則容器是Up,但這不是我想要的。可能是什麼原因?我看到volume mount行在與工作示例相反的事件日誌中缺失。與-v標誌泊塢窗運行命令使容器在已退出狀態

$ docker -v 
Docker version 17.09.0-ce, build afdb6d4 

Dockerfile

FROM ubuntu:16.04 

RUN apt-get update  
&& DEBIAN_FRONTEND=noninteractive apt-get install -y mysql-server  
&& sed -i "s/127.0.0.1/0.0.0.0/g" /etc/mysql/mysql.conf.d/mysqld.cnf  
&& mkdir /var/run/mysqld  
&& chown -R mysql:mysql /var/run/mysqld 

VOLUME ["/var/lib/mysql"] 

EXPOSE 3306 

CMD ["mysqld_safe"] 

這是不工作。

$ docker run -i -t -d -v /home/ubuntu/data:/var/lib/mysql --name mysql_container mysql_image 

事件日誌。

2017-11-... container create 08b44c094... (image=mysql_image, name=mysql_container) 

2017-11-... network connect 62bb211934... (container=08b44c094..., name=bridge, type=bridge) 

2017-11-... container start 08b44c094... (image=mysql_image, name=mysql_container) 

2017-11-... container die 08b44c094... (exitCode=0, image=mysql_image, name=mysql_container) 

2017-11-... network disconnect 62bb211934... (container=08b44c094..., name=bridge, type=bridge) 

集裝箱日誌。

$ docker logs -t mysql_container 
2017-11-... mysqld_safe Logging to syslog. 
2017-11-... mysqld_safe Logging to '/var/log/mysql/error.log'. 
2017-11-... mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 

此作品,未經-v

$ docker run -i -t -d --name mysql_container mysql_image 

事件日誌。

2017-11-... container create 84993141... (image=mysql_image, name=mysql_container) 

2017-11-... network connect 62bb2119... (container=84993141..., name=bridge, type=bridge) 

2017-11-... volume mount 8c36b53d33... (container=84993141...7, destination=/var/lib/mysql, driver=local, propagation=, read/write=true) 

2017-11-... container start 84993141... (image=mysql_image, name=mysql_container) 

集裝箱日誌。

$ docker logs -t mysql_container 
2017-11-... mysqld_safe Logging to syslog. 
2017-11-... mysqld_safe Logging to '/var/log/mysql/error.log'. 
2017-11-... mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 
2017-11-... mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 
+0

爲什麼不使用mysql容器? https://hub.docker.com/_/mysql/ – JazZ

+0

因爲我正在嘗試學習的東西,因爲我在這個階段是新手。 – BentCoder

沙发
0
1

這是一個有點複雜但有趣的案例。

那麼如何檢查發生了什麼?使用下面的命令:

docker run -i -t -v /tmp/data:/var/lib/mysql mysql_image bash 

現在你在裏面容器所以讓我們嘗試命令:

mysqld_safe 

而且它的結束,但讓我們看看/var/log/mysql/error.log 我們看到有:

2017-11-25T17:22:24.006180Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 
2017-11-25T17:22:24.006211Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 
2017-11-25T17:22:24.006221Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation. 
2017-11-25T17:22:24.006229Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory. 
2017-11-25T17:22:24.006237Z 0 [ERROR] InnoDB: Cannot open datafile './ibdata1' 

好的,我們的請參閱/var/lib/mysql外觀如何不使用體積映射:

[email protected]:/var/lib/mysql# ls -lah 
total 109M 
drwx------ 5 mysql mysql 4.0K Nov 25 17:24 . 
drwxr-xr-x 1 root root 4.0K Nov 25 17:13 .. 
-rw-r----- 1 mysql mysql 56 Nov 25 17:13 auto.cnf 
-rw-r--r-- 1 root root  0 Nov 25 17:13 debian-5.7.flag 
-rw-r----- 1 mysql mysql 419 Nov 25 17:13 ib_buffer_pool 
-rw-r----- 1 mysql mysql 48M Nov 25 17:13 ib_logfile0 
-rw-r----- 1 mysql mysql 48M Nov 25 17:13 ib_logfile1 
-rw-r----- 1 mysql mysql 12M Nov 25 17:13 ibdata1 
drwxr-x--- 2 mysql mysql 4.0K Nov 25 17:13 mysql 
drwxr-x--- 2 mysql mysql 4.0K Nov 25 17:13 performance_schema 
drwxr-x--- 2 mysql mysql 12K Nov 25 17:13 sys 
  1. 的mysql:mysql的是,目錄
  2. 我們有

很多MySQL的具體文件,讓我們看看我們有什麼用卷映射的所有者:

[email protected]:/var/lib/mysql# ls -lah 
total 8.0K 
drwxr-xr-x 2 root root 4.0K Nov 25 17:22 . 
drwxr-xr-x 1 root root 4.0K Nov 25 17:13 .. 
  1. Docker以root用戶映射這個目錄Docker正在映射這個d irectory到主機,所以所有的文件消失,因爲在主機上,該目錄是空的

如何獲得此工作? 更改您的命令:

CMD chown -R mysql:mysql /var/lib/mysql && if [ ! -c /var/lib/mysql/ibdata1 ]; then mysqld --initialize-insecure; fi && mysqld_safe 

這是怎麼回事呢?

chown -R mysql:mysql /var/lib/mysql - get back mysql:mysql owner 
if [ ! -c /var/lib/mysql/ibdata1 ]; then mysqld --initialize-insecure; fi - recreate mysql files with root user without pass but only if files not already exists (required for next runs) 
mysqld_safe - run mysql 
+0

這是你的意思嗎? 'CMD [「chown -R mysql:mysql/var/lib/mysql && mysqld --initialize-insecure && mysqld_safe」] – BentCoder

+0

我遵循你的調試步驟,一切正常。但是,如果將'CMD [「mysqld_safe」]'改爲'CMD [「chown -R mysql:mysql/var/lib/mysql && mysqld --initialize-insecure && mysqld_safe]''',我會得到'--initialize指定的但是數據目錄中有文件。除非我誤解了你,否則在運行docker run時中止'錯誤。 – BentCoder

+0

不,不,我給你適當的CMD指令(沒有[]數組和不帶「」),看到這裏:https://docs.docker.com/engine/reference/builder/#cmd 「與shell形式不同, exec表單不會調用命令shell,這意味着正常的shell處理不會發生。「 我們需要shell來獲取工作:) –

1368
votes
answers
30 views
+10

SQL multiple column ordering

I am trying to sort by multiple columns in SQL, and in different directions. column1 would be sorted descending, and column2 ascending.

How can I do this?

up vote 905 down vote accepted favorite
沙发
+9050
+50
ORDER BY column1 DESC, column2

This sorts everything by column1 (descending) first, and then by column2 (ascending, which is the default) whenever the column1 fields for two or more rows are equal.

+3170

The other answers lack a concrete example, so here it goes:

Given the following People table:

 FirstName |  LastName   |  YearOfBirth
----------------------------------------
  Thomas   | Alva Edison |   1847
  Benjamin | Franklin    |   1706
  Thomas   | More        |   1478
  Thomas   | Jefferson   |   1826

If you execute the query below:

SELECT * FROM People ORDER BY FirstName DESC, YearOfBirth ASC

The result set will look like this:

 FirstName |  LastName   |  YearOfBirth
----------------------------------------
  Thomas   | More        |   1478
  Thomas   | Jefferson   |   1826
  Thomas   | Alva Edison |   1847
  Benjamin | Franklin    |   1706
+1290
SELECT  *
FROM    mytable
ORDER BY
        column1 DESC, column2 ASC
+130

Multiple column ordering depends on both column's corresponding values: Here is my table example where are two columns named with Alphabets and Numbers and the values in these two columns are asc and desc orders.

enter image description here

Now I perform Order By in these two columns by executing below command:

enter image description here

Now again I insert new values in these two columns, where Alphabet value in ASC order:

enter image description here

and the columns in Example table look like this. Now again perform the same operation:

enter image description here

You can see the values in the first column are in desc order but second column is not in ASC order.

+40

You can use multiple ordering on multiple condition,

ORDER BY 
     (CASE 
        WHEN @AlphabetBy = 2  THEN [Drug Name]
      END) ASC,
    CASE 
        WHEN @TopBy = 1  THEN [Rx Count]
        WHEN @TopBy = 2  THEN [Cost]
        WHEN @TopBy = 3  THEN [Revenue]
    END DESC